topic Re: Looping through a large number of numeric values in SAS Programming

Looping through a large number of numeric values

apolitical — Tue, 13 Jun 2017 21:06:06 GMT

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.

Re: Looping through a large number of numeric values

Astounding — Tue, 13 Jun 2017 22:11:36 GMT

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?

Re: Looping through a large number of numeric values

apolitical — Tue, 13 Jun 2017 22:32:24 GMT

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.

Re: Looping through a large number of numeric values

kiranv_ — Tue, 13 Jun 2017 22:39:05 GMT

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

Re: Looping through a large number of numeric values

Astounding — Tue, 13 Jun 2017 23:17:13 GMT

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;

Re: Looping through a large number of numeric values

Shmuel — Tue, 13 Jun 2017 23:22:42 GMT

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;

Re: Looping through a large number of numeric values

s_lassen — Wed, 14 Jun 2017 05:57:31 GMT

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

Re: Looping through a large number of numeric values

apolitical — Fri, 16 Jun 2017 20:17:00 GMT

thanks . i ended up not going forward with this, but this works.