Looping over each item of a macro variable

[windy]

Hi all, 

I wrote a loop to run over each item stored in a macro variable, but the loop is partly working only. 

I first write a macro called getdata and then write another macro called call_getdata to do the loop for each value of year and type stored in the macro variables. 

If &year = "all", I want to get all observations. Similarly, if &type = "all", all observations are chosen. Otherwise, it only selects the data related to a particular year and type. I have a variable year and indicator in the datain sample containing the values of the years (2018, 2019, 2020, and 2021) and types (typea, typeb, typec)

When the loop goes over specific values of year and type, it's working well. But when it goes over the first value "all", the loop doesn't work. 

The error message I received is "Variable all is not on file work.datain". 

When I specify option mprint, I saw that the way SAS reads the first %if %then %do %end is that it will select the observation whether year = "all" or type = "all". 

Could you please help me to fix it?

%macro getdata(datain,year,type);
/*get data*/
%if &year = "all" %then %do;
data data_out; set &datain; run;
%end;
%else %do;
data data_out; set &datain;
where year = &year;
run;
%end;

%if &type = "all" %then %do;
data datain_out_&type; set data_out; run;
%end;
%else %do;
data data_out_&type; set data_out;
where indicator = "&type";
run;
%end;
%mend;

%macro call_getdata();
%let years = all 2018 2019 2020 2021;
%let types =all typea typeb typec;
%local i j;
%do i=1 %to %sysfunc(countw(&years,%str( )));
%let year = %scan(&years,&i,%str( ));
%do j=1 %to %sysfunc(countw(&types,%str( )));
%let type = %scan(&types,&j,%str( ));

%let year = &year;
%let type = &type;
 
%getdata(datain=datain,year=&year,type=&type);

%end;
%end;

%mend;
%call_getdata();

Thank you so much in advance for any help. 

[ballardw]

It really is not clear where you show an example passing a value of "all".  You need to show the code example where you pass a value of "all" for year, and another for type.

Since macro processor basically expects character values it almost never is a good idea to use quotes around values.

When you use

%if year="all" %then <whatever>

YOU have to pass the value of "all" including the quotes. Example:

%macro dummy(parm);
   %if &parm="all" %then %put "all" passed as parameter;
   %if &parm= all %then %put all pased as parameter;
%mend;

%dummy(all)
%dummy("all")

Debug macros that are misbehaving by setting options Mprint:

Options mprint;
<macro to test>
options nomprint; /* to turn off the option*/

This will display the text generated by the macro in the LOG so you can see the values passed of the parameters. You could add SYMBOLGEN, to see details of how complex macro variables resolve, or MLOGIC to see how macro logical comparisons are built.

Please post code into a text or code box, opened on the forum with the </> or "running man" icon above the main message window. That makes it easier to tell which is code and question. It will also maintain indenting. The main message window will reformat text and may reformat code so that it will not run by replacing white space with html that isn't seen.

[PaigeMiller]

%let years = all 2018 2019 2020 2021;

For most SAS procedures, such a macro would not be needed and separate data sets are not needed. Most SAS procedures have a BY statement which repeats whatever analysis you want for each YEAR — and if necessary, you could simply repeat the code without the BY to have the analysis done on all data. If you just want means and other simple statistics, this last part of repeating the analysis code without the BY isn't even necessary.

So before you struggle with all these macros, explain why you need this, and why using a BY statement isn't sufficient.

[windy]

Hi @PaigeMiller 

The actual loop is more complicated than this. I am doing a bunching estimation which the count of observations for each bin is essential. The count does not need to be in year or type. For each dataset, I will create variables using the counts and run a do-while loop. Finally, based on the optimized results from the loop, I will compute some estimators. That's why I think separating the sample from the beginning could make it easier. 

[PaigeMiller]

---

@windy wrote:

Hi @PaigeMiller 

The actual loop is more complicated than this. I am doing a bunching estimation which the count of observations for each bin is essential. The count does not need to be in year or type. For each dataset, I will create variables using the counts and run a do-while loop. Finally, based on the optimized results from the loop, I will compute some estimators. That's why I think separating the sample from the beginning could make it easier. 

---

Okay, @windy , thanks.

I am still skeptical that all of this macro code generation is needed (it might be, it's hard to tell). If you'd like to explore this further, the possibility of doing whatever you are doing without macros, let us know. We can talk about the details of what you are doing and whether or not the macro approach is the best way to go.

[Tom · Accepted solution]

This part of your code is not going to work:

%if &type = "all" %then %do;
data datain_out_&type; set data_out; run;
%end;
%else %do;
data data_out_&type; set data_out;
where indicator = "&type";
run;
%end;

If TYPE is equal to "all" then it will attempt to generate this SAS code:

data datain_out_"all"; set data_out; run;

which has an invalid dataset name.

You probably meant to use one of these tests instead:

%if "&type" = "all" %then %do;
%if &type = all %then %do;
%if %qupcase(&type) = ALL %then %do;

The last one is the most flexible.

[Quentin]

---

@Tom wrote:

If TYPE is equal to "all" then it will attempt to generate this SAS code:

data datain_out_"all"; set data_out; run;

which has an invalid dataset name.

But, surprisingly to me, doesn't actually generate an error.  It just ignores the quoted code:

1    data datain_out_"all";
2    run;

NOTE: The data set WORK.DATAIN_OUT_ has 1 observations and 0 variables.
NOTE: The data set all has 1 observations and 0 variables

If you put a space inside the quoted text, it will error:

3    data datain_out_"all all";
4    run;

ERROR: The value 'ALL ALL'n is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.

[yabwon]

it does not ignore it:

NOTE: The data set all has 1 observations and 0 variables

check out what do you have in  the "." directory

I'll bet 5$ you will find there file named "all"

Bart

[Quentin]

Indeed, I managed to paste the log and didn't read it.  And here I am trying to give suggestions on debugging methods. : )

Yes, it is there in the working directory.  I'll happily pay you @yabwon the first time we get to meet in person.

[Tom]

---

@yabwon wrote:

it does not ignore it:

NOTE: The data set all has 1 observations and 0 variables

check out what do you have in  the "." directory

I'll bet 5$ you will find there file named "all"

Bart

---

So if is treating if as if there was a space between the two names.

data test "all";
  set sashelp.class;
run;

That syntax will create TEST in the WORK directory (or USER directory is using that option).

But the ALL dataset will be created in the present working directory.

And if you don't have access to write in the PWD then you will get an error:

 73         %let x="ALL";
 74         data xx&x ;
 75         run;
 
 ERROR: User does not have appropriate authorization level for library WC000001.
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.XX may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       

[Quentin]

Thanks @yabwon  and @Tom .  Makes sense.  I forgot that you can do stuff like:

data "Q:\junk\me" ;
run ;

and it will happily write Q:\junk\me.sas7bdat.

[yabwon]

I wonder if it would work with "/dec/null" under linux 🙂

Bart

[Tom]

No.  "/dev/null" would mean a file named "/dev/null.sas7bdat" and Unix will not let your write a file into the /dev directory.

[yabwon]

it's like:

data "C:\data\file1";
  set sashelp.class;
run;

[windy]

Thanks so much, Tom. You helped me again.