10-20-2017 03:54 PM
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 $3 code1-code8 (ZAR ZCH ZHN ZLA ZMI ZNK ZNY ZSF);
%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";
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
10-20-2017 03:58 PM
I have used arrays in evaluation statement before that were inside of loops, and they worked. The following worked:
%let topMetros = array topMetros $5 metro1-metro10 ("35620" "31080" "33100" "16980" "41860" "47900" "26420" "19100" "41940" "14460");
%do i = 1 %to &toomany;
data geog.uscis_ois_lpr_s5_mtr&i. (drop = metro;
if CBSA_CODE = topMetros[&i.] then
10-20-2017 04:05 PM
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.
10-20-2017 04:05 PM
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);
10-20-2017 04:09 PM
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....
10-20-2017 04:14 PM
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.
10-20-2017 04:20 PM
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;
10-20-2017 04:26 PM
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.
10-20-2017 04:23 PM
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.