I'm trying to do conditional analyses based on a variable taking on every possible unique values there is. Say I want to do a proc freq on Var2 conditional on Var1 being equal to one of the many values it could take on. I used to do the following thing to grab all unique values of Var1 before looping through it:
proc sql;
select distinct Var1 into: allvalues separated by ' ' from work.dataname
quit;
That worked well for categorical variables with limited number of values, but now Var1 is numeric and can take on 100,000 possible values. Doing the procedure above would crash SAS becasue the string gets too long. Can someone write a macro for what I want to achieve including the following frequency procedure?
proc freq data=dataname;
table Var2;
where Var1=(looping through all unique values taken on by Var1);
run;
Thank you.
Well, whatever approach you take you will still have 100,000 sets of reports (assuming you can wait for the program to finish). But I leave that part to you. Here is a way to approach this that will make this feasible.
%macro every_a (dsn);
proc sql;
create table every_a as select distinct a from &dsn;
quit;
%local i a_value;
%do i=1 %to &sqlobs;
data _null_;
set every_a (firstobs=&i);
call symputx('a_value', a);
stop;
end;
*** Add what you would like here, to process the current value of A which is stored in &A_VALUE;
%end;
%mend every_a;
And what is your plan to utilize 100,000 reports?
You can easily get what you are asking for:
proc sort data=have;
by var1;
run;
proc freq data=have;
by var1;
tables var2;
run;
But what happens next?
if possible, please show a small representative sample what you have and what you want. So that it will easy for someone to help you
Well, whatever approach you take you will still have 100,000 sets of reports (assuming you can wait for the program to finish). But I leave that part to you. Here is a way to approach this that will make this feasible.
%macro every_a (dsn);
proc sql;
create table every_a as select distinct a from &dsn;
quit;
%local i a_value;
%do i=1 %to &sqlobs;
data _null_;
set every_a (firstobs=&i);
call symputx('a_value', a);
stop;
end;
*** Add what you would like here, to process the current value of A which is stored in &A_VALUE;
%end;
%mend every_a;
Can bext skilton code help you?
proc sql;
create table list as
select distinct Var1
from work.dataname
order by var1;
quit;
proc sort dataname; by var1; run;
data want;
merge list dataname;
by var1;
if first.var1 then do; <initializing code>; end;
... code to be done taken from the "loop" ...
if last.var1 then do; <finalizing code>; end;
run;
@apolitical: assuming from your initial question that you already have a macro which does the report for one value. You could then either use CALL EXECUTE or write the statements to a file for %INCLUDE. I prefer the latter, as you have the option to take a look at your generated file before submitting, and you can submit it statement by statement when testing:
filename tempsas temp; data _null_; set dataname; by Var1; if first.Var1; file tempsas; put '%report_macro(' Var1 ');'; run;
When developing/testing you can then open the TEMPSAS file, take a good look at it, and if it looks OK you can use the SUBTOP command to test the first couple of statements.
When the test is OK, you can instead put the line
%include tempsas;
into your code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.