I have a dataset like Cars1 (generate it with the code below), with years in the variable name.
%macro test1();
data cars1;
set sashelp.cars (keep = make model);
%do i = 2012 %to 2014;
make&i. = make;
%end;
run;
%mend test1;
%test1();
Which looks something like this...
Make Model make2012 make2013 make2014
Acura MDX Acura Acura Acura
Acura RSX Type S 2dr Acura Acura Acura
Acura TSX 4dr Acura Acura Acura
I want to iterate over the 3 years (2012-2014), creating a categorical dataset (make_2012_cat, etc.) for each of the 3 variables I created in Cars1, based on comparison with the respective value. So if make2012 eq 'Acura' I have a statement for that. Same for make2021 ne 'Acura'. For some reason, the comparison is not working, and I end up with the default value each time ("It's not comparable :-(")
%macro test2();
data cars2;
set cars1;
%do i = 2012 %to 2014;
%if make&i. eq "Acura" %then %do;
make_&i._cat = "It's an Acura!";
%end;
%if make&i. ne "Acura" %then %do;
make_&i._cat = "It's not an Acura.";
%end;
%else %do;
make_&i._cat = "It's not comparable :-(";
%end;
%end;
run;
%mend test2;
%test2();
I'm ending up only with the default value. I've tried this inside and outside a macro and with lots of adjustments to the way I call the iterator, i. Also have tried declaring global variables, but at this point I'm just spinning my wheels. Any help is appreciated!!
Make make_2012_cat make_2013_cat make_2014_cat
Acura It's not an Acura. It's not an Acura. It's not an Acura.
Acura It's not an Acura. It's not an Acura. It's not an Acura.
Acura It's not an Acura. It's not an Acura. It's not an Acura.
So, you need to understand the difference between the macro language and the data step language. These are two separate things that don't actually do the same thing.
%if and other macro language stuff only affect the text of the code you are compiling. They change the SAS program to some other SAS program. But they don't have anything to do with the data!
Steps:
So you need to separate the data step (if) from the macro language (%if).
%do i = 2012 %to 2014;
%if make&i. eq "Acura" %then %do;
make_&i._cat = "It's an Acura!";
%end;
%if make&i. ne "Acura" %then %do;
make_&i._cat = "It's not an Acura.";
%end;
%else %do;
make_&i._cat = "It's not comparable :-(";
%end;
%end;
Turn that into
%do i = 2012 %to 2014;
if make&i. eq "Acura" then do;
make_&i._cat = "It's an Acura!";
end;
else if make&i. ne "Acura" then do; **note I added 'else' here;
make_&i._cat = "It's not an Acura.";
end;
else do;
make_&i._cat = "It's not comparable :-(";
end;
%end;
The macro language is there doing its part - it's making
if make_2012_cat = ...;
if make_2013_cat = ...;
if make_2014_cat = ...;
But the data step language is actually what's doing the work of comparing make_2012_cat variable value to 'Acura'.
Note one other thing; this isn't necessarily very idiomatic SAS. It's legal, and a TON of people do this, but it's not truly using SAS features to the utmost. Better would be to have make_cat and then different rows for 2012,2013,2014, if you can. Maybe you can't - maybe you have a good reason for that - but it's better when your variable name doesn't contain data (which these do). Maybe your real example is different, but I find that about 80% of the time when I see people doing things roughly like this, it could be done better in a different data structure.
To do this entirely in the data step, you need arrays.
data cars1;
set sashelp.cars (keep = make model);
array makes[3] make_2012-make_2014;
do _i = 1 to dim(makes);
makes[_i] = make;
end;
run;
data cars2;
set cars1;
array makes[3] make_2012-make_2014;
array makes_cat[3] make_cat_2012-make_cat_2014; *slightly different, easier;
do _i = 1 to dim(makes);
if makes[_i] eq "Acura" then makes_cat[_i] = "It's an Acura!";
else if makes[_i] ne "Acura" then makes_cat[_i] = "It's not an Acura.";
end;
run;