## Looping through a large number of numeric values

Solved
Frequent Contributor
Posts: 87

# Looping through a large number of numeric values

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.

Accepted Solutions
Solution
‎06-16-2017 04:16 PM
Super User
Posts: 6,629

## Re: Looping through a large number of numeric values

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;

All Replies
Super User
Posts: 6,629

## Re: Looping through a large number of numeric values

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?

Frequent Contributor
Posts: 87

## Re: Looping through a large number of numeric values

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.
PROC Star
Posts: 503

## Re: Looping through a large number of numeric values

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

Solution
‎06-16-2017 04:16 PM
Super User
Posts: 6,629

## Re: Looping through a large number of numeric values

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;

Frequent Contributor
Posts: 87

## Re: Looping through a large number of numeric values

thanks . i ended up not going forward with this, but this works.
Posts: 1,831

## Re: Looping through a large number of numeric values

[ Edited ]

``````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;``````
PROC Star
Posts: 253

## Re: Looping through a large number of numeric values

@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;`

☑ This topic is solved.