DATA Step, Macro, Functions and more

Macro Loop with Array Value in Where Statement

Reply
Occasional Contributor
Posts: 17

Macro Loop with Array Value in Where Statement

Hi, All.

 

I am trying to streamline my code to use a single frequency procedure code bloc one inside a loop of 8 iterations. But as always with the macros, it is something that should work, but there is something about the chewed bubblegum, duct-taped, cotton-plugged wiring of the way SAS manages macros that is not working. The following code is based off a similar routine that I wrote that used macro variable in the IF statement -and worked- but does not here. I have tried multiple ways of specifying the values in the array, as well as worked with the ampersands and quotes to no avail.

 

%let officeCodes = array officeCodes[8] $3 code1-code8 (ZAR ZCH ZHN ZLA ZMI ZNK ZNY ZSF);

 

%macro count(toomany);

     %do i = 1 %to &toomany;

           proc freq data =  risk.rapsPending_s2a (where = (RARCASE_CCO_CODE_2 =  "&&officeCodes[&i.]")) order = freq;

                format RARAPPL_CNTRY_OF_BIRTH_CODE $cntry.;

                tables RARAPPL_CNTRY_OF_BIRTH_CODE / missing;

                title "Country of Birth";

           run;

     %end;

%mend count;

%count(8);

 

I get the following message. From everything I know and looked up, it should work.  I really hate the way SAS implements macros…. Seems like they just kept gluing cotton on to duct-tape balls that held loose wiring together given the way this software developed.  It’s always the macros.

 

NOTE: No observations were selected from data set RISK.RAPSPENDING_S2A.

NOTE: There were 0 observations read from the data set RISK.RAPSPENDING_S2A.

      WHERE 0 /* an obviously FALSE WHERE clause */ ;

NOTE: PROCEDURE FREQ used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

Ron

Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Posted in reply to RedMcCallen

Arrays don't work that way. There's no such thing as a SAS macro array.

 

 

Occasional Contributor
Posts: 17

Re: Macro Loop with Array Value in Where Statement

I have used arrays in evaluation statement before that were inside of loops, and they worked. The following worked:

 

%let topMetros = array topMetros[10] $5 metro1-metro10 ("35620" "31080" "33100" "16980" "41860" "47900" "26420" "19100" "41940" "14460");

%macro count(toomany);
%do i = 1 %to &toomany;
data geog.uscis_ois_lpr_s5_mtr&i. (drop = metroSmiley Happy;
set geog.uscis_ois_lpr_s5_metros;
&topMetros;

if CBSA_CODE = topMetros[&i.] then
output geog.uscis_ois_lpr_s5_mtr&i.;

run;

%end;
%mend count;
%count(10);

Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Posted in reply to RedMcCallen

Yes, because that's fully valid SAS syntax. Your current one is not, the code generated needs to be valid SAS code. This is not. Do a text replacement and you'll see that there's several invalid SAS syntax issues.

 

Arrays are valid only in data steps, but not in PROC FREQ for sure. 

Frequent Contributor
Posts: 149

Re: Macro Loop with Array Value in Where Statement

Posted in reply to RedMcCallen

Untested code, you should add a check to verify that &i is not higher than %sysfunc(countw(&officeCodes))

%let officeCodes = ZAR ZCH ZHN ZLA ZMI ZNK ZNY ZSF;
 
%macro count(toomany);
     %do i = 1 %to &toomany;
           proc freq data =  risk.rapsPending_s2a (where = (RARCASE_CCO_CODE_2 =  "%scan(&officeCodes, &i")) order = freq;
                format RARAPPL_CNTRY_OF_BIRTH_CODE $cntry.;
                tables RARAPPL_CNTRY_OF_BIRTH_CODE / missing;
                title "Country of Birth";
           run;
     %end;
%mend count;
%count(8);
Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Posted in reply to error_prone
           proc freq data =  risk.rapsPending_s2a (where = (RARCASE_CCO_CODE_2 in ( "ZCH" "ZHN") )  order = freq;

                by RARCASE_CCO_CODE_2;

                format RARAPPL_CNTRY_OF_BIRTH_CODE $cntry.;
                tables RARAPPL_CNTRY_OF_BIRTH_CODE / missing;
                title "Country of Birth";
           run;

Is there a specific reason to not use BY groups here? It seems like it would be more efficient and less code.... 

Occasional Contributor
Posts: 17

Re: Macro Loop with Array Value in Where Statement

The intent of the code is to produce a frequency output for each of the eight offices. I don't want to write eight Proc Freq code blocs for each office,. From the way I read the use of the in function here it is treating it like a single evaluation for any of the eight offices. And if one of the offices evaluates true for the in function, then produce the output.

Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Posted in reply to RedMcCallen

That's incorrect, note the addition of the BY statement as well. The BY statement creates a table for each unique value of the BY group.

 

Here's a fully worked example that you can run.

 

proc sort data=sashelp.class out=class; by sex; run;

proc freq data=class;
by sex;

table age;
run;

 

Occasional Contributor
Posts: 17

Re: Macro Loop with Array Value in Where Statement

Yup... I see that now. I was just unaware that the BY statement would do that to the IN function. It's these types of things that makes me like SAS so much. It's just the macros that drive me crazy. From years of programming statistical software in a language, macro programming is just something that I can't fit my mind to because it just seem so over-complicated and lacking in functioning well with base code.

Occasional Contributor
Posts: 17

Re: Macro Loop with Array Value in Where Statement

Ok... I executed this code and I see what it does now.  Thank you. I did not realize the BY statement would treat it as separate instances for each element inside the IN function. I like this better because it keeps the code simple and efficient, which is always what I am looking for over elegance and showing off as a number of programmers like to do. I think I still have the problem -a minor one- which is that I wanted the title of each of the eight offices to show up after the Country of Birth title. Not sure I can get that here, but that is a minor issue.

 

Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Posted in reply to RedMcCallen

You can control the titles and BY Value titles. 

 

Google #BYVAL and you'll find examples. 

 

 

Super User
Posts: 19,878

Re: Macro Loop with Array Value in Where Statement

Occasional Contributor
Posts: 17

Re: Macro Loop with Array Value in Where Statement

Posted in reply to error_prone

That throws a whole number of errors. 

Ask a Question
Discussion stats
  • 12 replies
  • 92 views
  • 0 likes
  • 3 in conversation