topic Re: Looping over each item of a macro variable in SAS Programming

Looping over each item of a macro variable

windy — Thu, 26 Jan 2023 04:16:28 GMT

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.

Re: Looping over each item of a macro variable

ballardw — Thu, 26 Jan 2023 04:00:55 GMT

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.

Re: Looping over each item of a macro variable

PaigeMiller — Thu, 26 Jan 2023 11:43:58 GMT

%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.

Re: Looping over each item of a macro variable

windy — Thu, 26 Jan 2023 13:33:17 GMT

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.

Re: Looping over each item of a macro variable

Tom — Thu, 26 Jan 2023 13:35:29 GMT

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.

Re: Looping over each item of a macro variable

PaigeMiller — Thu, 26 Jan 2023 13:45:38 GMT

@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.

Re: Looping over each item of a macro variable

yabwon — Thu, 26 Jan 2023 13:53:46 GMT

A bit off-topic but:

1) Why not do the data read only one time (see macro getdata_short)? [I fixed your `"all"` error in the code]

data datain;
 do year = 2018 to 2023;
    do indicator = "typea","typeb","typec";
      x = ranuni(42);
      output;
    end;
 end;
run;
proc print;
run;


%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 data_out_&type; set data_out; run;
%end;
%else %do;
data data_out_&type; set data_out;
where indicator = "&type";
run;
%end;
%mend;



%macro getdata_short(datain,year,type);
data data2_out_&type; 
  set &datain;
  where 1
  %if &year NE all %then 
    %do;
      and year = &year 
    %end;
  %if &type NE all %then 
    %do;
      and indicator = "&type" 
    %end;
  ;
run;
%mend;

options mprint;
%getdata(datain,2020,typec);
%getdata_short(datain,2020,typec);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

options mprint;
%getdata(datain,all,typec);
%getdata_short(datain,all,typec);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

options mprint;
%getdata(datain,all,all);
%getdata_short(datain,all,all);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

2) Other thing, if you execute:

%getdata(datain,2020,typec);

%getdata(datain,2021,typec);

the result of the second execution overwrites result of the first one, maybe adding the "year" value to the data set name?

3) Since you are reading datain dataset several times to create several outputs, why not to read it only one time and use dedicated "output" statement?

%macro getdata_shorter(datain,years,types);
%local i ni j nj year type;

%let ni = %sysfunc(countw(%superq(years)));
%let nj = %sysfunc(countw(%superq(types)));

data
%do i = 1 %to &ni.;
  %do j = 1 %to &nj.;
    %let year = %scan(%superq(years),&i.);
    %let type = %scan(%superq(types),&j.);
    data_out_&year._&type.
  %end;
%end;
;
 
set &datain;

select;
  %do i = 1 %to &ni.;
    %do j = 1 %to &nj.;
      %let year = %scan(%superq(years),&i.);
      %let type = %scan(%superq(types),&j.);
        when (1=1
        %if &year NE all %then 
          %do;
            and year = &year 
          %end;
        %if &type NE all %then 
          %do;
            and indicator = "&type" 
          %end;
        ) output data_out_&year._&type.;
      %end;
  %end;

  otherwise;
end;

run;
%mend;


options mprint;
%getdata_shorter(datain,2020 2021 all,typec typeb all);

Log:

MPRINT(GETDATA_SHORTER):   data data_out_2020_typec data_out_2020_typeb data_out_2020_all data_out_2021_typec data_out_2021_typeb
data_out_2021_all data_out_all_typec data_out_all_typeb data_out_all_all ;
MPRINT(GETDATA_SHORTER):   set datain;
MPRINT(GETDATA_SHORTER):   select;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 and indicator = "typec" ) output data_out_2020_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 and indicator = "typeb" ) output data_out_2020_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 ) output data_out_2020_all;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 and indicator = "typec" ) output data_out_2021_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 and indicator = "typeb" ) output data_out_2021_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 ) output data_out_2021_all;
MPRINT(GETDATA_SHORTER):   when (1=1 and indicator = "typec" ) output data_out_all_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and indicator = "typeb" ) output data_out_all_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 ) output data_out_all_all;
MPRINT(GETDATA_SHORTER):   otherwise;
MPRINT(GETDATA_SHORTER):   end;
MPRINT(GETDATA_SHORTER):   run;

NOTE: There were 18 observations read from the data set WORK.DATAIN.
NOTE: The data set WORK.DATA_OUT_2020_TYPEC has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2020_TYPEB has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2020_ALL has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_TYPEC has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_TYPEB has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_ALL has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_TYPEC has 4 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_TYPEB has 4 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_ALL has 4 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

All the best

Bart

Re: Looping over each item of a macro variable

Quentin — Thu, 26 Jan 2023 15:04:07 GMT

Hi,

You've already got some great answers. Just wanted to add some debugging thoughts.

When you're debugging macros, it often makes sense to debug the SAS code first, then debug the macro. In the case where one macro calls another macro, you want to debug and test the called macro first, to get it working. In this case you thought the problem was in your looping macro, but it's actually in the called macro.

If you take your called macro:

%macro getdata(datain,year,type);
  %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;

You can test it with options MPRINT and MLOGIC turned on, to see what it does.

%getdata(datain,2020,typec) works.

%getdata(datain,all,typec) errors. You can see in the log that the condition &year="all" evaluated to false (because of the quote marks):

MLOGIC(GETDATA):  %IF condition &year = "all" is FALSE
MPRINT(GETDATA):   data data_out;
MPRINT(GETDATA):   set datain;
MPRINT(GETDATA):   where year = all;
ERROR: Variable all is not on file WORK.DATAIN.
MPRINT(GETDATA):   run;

%getdata(datain,"all",typec) works.

%getdata(datain,"all","all") does not error, but it gives a bad result. It outputs datain_out_ rather than datain_out_all, because of the quote marks:

MLOGIC(GETDATA):  %IF condition &type = "all" is TRUE
MPRINT(GETDATA):   data datain_out_"all";
MPRINT(GETDATA):   set data_out;
MPRINT(GETDATA):   run;

NOTE: There were 18 observations read from the data set WORK.DATA_OUT.
NOTE: The data set WORK.DATAIN_OUT_ has 18 observations and 3 variables.
NOTE: The data set all has 18 observations and 3 variables.

I actually would have thought that last example, with data datain_out_"all"; would error. I guess the compiler just ignores the quoted text.

You've already seen solutions, so I won't go further. Just wanted to suggest that when you're running macros, you should have MPRINT turned on so that you can see the SAS code that is generated, and when you're debugging it's sometimes helpful to turn on MLOGIC and SYMBOLGEN. More importantly, in complex macro settings, you want to debug the innermost macro first, and test it thoroughly.

Re: Looping over each item of a macro variable

Quentin — Thu, 26 Jan 2023 15:14:21 GMT

@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.

Re: Looping over each item of a macro variable

yabwon — Thu, 26 Jan 2023 15:17:41 GMT

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

Re: Looping over each item of a macro variable

Quentin — Thu, 26 Jan 2023 15:25:18 GMT

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.

Re: Looping over each item of a macro variable

Tom — Thu, 26 Jan 2023 15:28:36 GMT

@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

Re: Looping over each item of a macro variable

Quentin — Thu, 26 Jan 2023 15:32:31 GMT

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.

Re: Looping over each item of a macro variable

yabwon — Thu, 26 Jan 2023 15:34:44 GMT

it's like:

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

Re: Looping over each item of a macro variable

yabwon — Thu, 26 Jan 2023 15:35:57 GMT

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

Bart

Re: Looping over each item of a macro variable

Tom — Thu, 26 Jan 2023 15:48:58 GMT

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

Re: Looping over each item of a macro variable

windy — Fri, 27 Jan 2023 01:38:31 GMT

Thanks so much, Tom. You helped me again.

Re: Looping over each item of a macro variable

windy — Fri, 27 Jan 2023 01:42:17 GMT

Thanks for your suggestion. I like the way you do. I tried the code. The log I got is similar to what you showed here, but the number of obs in datasets when &year = all are not correct yet. I'll recheck to see what's going on.