BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
apolitical
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

7 REPLIES 7
Astounding
PROC Star

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?

apolitical
Obsidian | Level 7
Thanks. I am doing something that may require looping through the values. That frequency was just an example, I realize that it's not a good example.
kiranv_
Rhodochrosite | Level 12

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

Astounding
PROC Star

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;

apolitical
Obsidian | Level 7
thanks . i ended up not going forward with this, but this works.
Shmuel
Garnet | Level 18

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;
s_lassen
Meteorite | Level 14

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 709 views
  • 3 likes
  • 5 in conversation