topic Re: Macro Loop with Array Value in Where Statement in SAS Programming

Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 19:54:23 GMT

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

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 19:56:27 GMT

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

Re: Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 19:58:26 GMT

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 = metro:);
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);

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 20:05:29 GMT

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.

Re: Macro Loop with Array Value in Where Statement

error_prone — Fri, 20 Oct 2017 20:05:47 GMT

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);

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 20:09:00 GMT

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

Re: Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 20:10:14 GMT

That throws a whole number of errors.

Re: Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 20:14:10 GMT

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.

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 20:20:41 GMT

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;

Re: Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 20:23:16 GMT

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.

Re: Macro Loop with Array Value in Where Statement

RedMcCallen — Fri, 20 Oct 2017 20:26:36 GMT

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.

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 20:27:36 GMT

You can control the titles and BY Value titles.

Google #BYVAL and you'll find examples.

Re: Macro Loop with Array Value in Where Statement

Reeza — Fri, 20 Oct 2017 20:33:21 GMT

http://www2.sas.com/proceedings/sugi23/Coders/p75.pdf