Outputting macro elements for each iteration of a do loop to a dataset

Solved
Frequent Contributor
Posts: 82

Outputting macro elements for each iteration of a do loop to a dataset

This is probably very simple, but right now I am running something that gives me a total number of times I see each person.

So basically

PersonTotalnumviews
A5
B2
C3
D9

and then I use SQL to put the total of everything into a macro

proc sql noprint;

select sum(totalnumviews) into :totalviewstotal

from megasetfinallast_&&K;

quit;

data schedule_eval_&&K;

total=&totalviewstotal;

run;

Right now this code gives me a daaset for each time the do loop (K) is run. But JUst want a dataset that each row is another iteration of the loop.

So row 1 will give me totalviewstotal for the first iteration, row 2 for the second iteration, etc. What do I need to do?

Accepted Solutions
Solution
‎04-01-2013 11:50 AM
Super User
Posts: 23,728

Re: Outputting macro elements for each iteration of a do loop to a dataset

At the end of the loop you can add something like the following, Proc append doesn't require a table to be created originally.

Assuming you have K as your loop, why do you have double ampersand in the sql query?

proc sql noprint;

select sum(totalnumviews) into :totalviewstotal

from megasetfinallast_&&K;

quit;

data temp;

iteration=&k.;

totalviewstotal=&totaviewstotal;

run;

proc append base=want data=temp;

run;

*Clean up tables for space maintenance and prevent errors;

proc sql;

drop table temp;

quit;

%end;

*end of macro;

%mend;

All Replies
Super User
Posts: 8,114

Re: Outputting macro elements for each iteration of a do loop to a dataset

Not sure I have the whole picture, but why not just create an empty table before starting your looping.  Something like:

data totals ;

stop;

length iteration total 8;

run;

Then each time in the loop insert a record into the table.

proc sql ;

insert into totals

select &K as iteration

, sum(totalnumviews) as total

from megasetfinallast_&K

;

quit;

Frequent Contributor
Posts: 82

Re: Outputting macro elements for each iteration of a do loop to a dataset

Hmm, it seems to only ouput the most recent iteration, and when it does it gives me the warning "INto clause that is not in the outermost query block will be ignored"

Super User
Posts: 23,728

Re: Outputting macro elements for each iteration of a do loop to a dataset

If you're looping and want counts for each loop, typically you use a proc append at the end of the loop to stack your datasets for each loop.

Frequent Contributor
Posts: 82

Re: Outputting macro elements for each iteration of a do loop to a dataset

So the idea is that each row of this dataset (totals in this case) will be the results of each iteration of the do loop.

For each iteration, the program is currently giving me a dataset that looks like this:

PlayerTotalnumviews
15
24
39
42

Each time an iteration of the overall do loop is run, I will get a different table like the one above. So each time, I want a row to be outputed to the dataset that would say

IterationTotal number of views
1800
2742

And another row would just be added for each time the do loop is run.

Super User
Posts: 8,114

Re: Outputting macro elements for each iteration of a do loop to a dataset

Looks like you have a typo that has caused SAS to confuse the INTO keyword that is part of the INSERT INTO syntax for adding records to a table with the INTO :macrovar syntax that is part of a SELECT statement.

There is no need to create macro variables, unless you need them for some other purpose.  It is much more straight forward just write directly to the data sets without first generating macro variables.

Frequent Contributor
Posts: 82

Re: Outputting macro elements for each iteration of a do loop to a dataset

hmm, i got rid of it going to macro variable and it got rid of the warning, but still only ouputs the latest iteration. So the first row now says iteration is 2, with a value for total.

Super User
Posts: 8,114

Re: Outputting macro elements for each iteration of a do loop to a dataset

So it sounds like you placed after the end of the loop instead of inside the loop.

Frequent Contributor
Posts: 82

Re: Outputting macro elements for each iteration of a do loop to a dataset

Nah, it is right before the loop ends.It works if I try to output a new dataset for each set of summary statistics for each iteration (meaning id have20 different datasets for 20 iterations). probably something im missing though.

Super User
Posts: 8,114

Re: Outputting macro elements for each iteration of a do loop to a dataset

Make sure you are not blowing away the summary table inside the loop.

So your code flow should look like:

<Initialize summary table >

do ....

<calculate new numbers>

<append new record to summary table>

end..

Do something with the summary table.

Solution
‎04-01-2013 11:50 AM
Super User
Posts: 23,728

Re: Outputting macro elements for each iteration of a do loop to a dataset

At the end of the loop you can add something like the following, Proc append doesn't require a table to be created originally.

Assuming you have K as your loop, why do you have double ampersand in the sql query?

proc sql noprint;

select sum(totalnumviews) into :totalviewstotal

from megasetfinallast_&&K;

quit;

data temp;

iteration=&k.;

totalviewstotal=&totaviewstotal;

run;

proc append base=want data=temp;

run;

*Clean up tables for space maintenance and prevent errors;

proc sql;

drop table temp;

quit;

%end;

*end of macro;

%mend;

Frequent Contributor
Posts: 82

Re: Outputting macro elements for each iteration of a do loop to a dataset

this seems to be working. Thanks a bunch

🔒 This topic is solved and locked.