topic Re: Outputting macro elements for each iteration of a do loop to a dataset in SAS Programming

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

Xamius32 — Sun, 31 Mar 2013 21:16:36 GMT

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

Person	Totalnumviews
A	5
B	2
C	3
D	9

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?

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

Tom — Mon, 01 Apr 2013 02:29:36 GMT

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;

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

Xamius32 — Mon, 01 Apr 2013 15:12:09 GMT

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"

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

Reeza — Mon, 01 Apr 2013 15:21:34 GMT

I think you need to show more of what you're doing.

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.

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

Tom — Mon, 01 Apr 2013 15:27:37 GMT

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.

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

Xamius32 — Mon, 01 Apr 2013 15:30:40 GMT

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:

Player	Totalnumviews
1	5
2	4
3	9
4	2

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

Iteration	Total number of views
1	800
2	742

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

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

Xamius32 — Mon, 01 Apr 2013 15:36:15 GMT

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.

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

Tom — Mon, 01 Apr 2013 15:46:05 GMT

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

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

Xamius32 — Mon, 01 Apr 2013 15:50:27 GMT

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.

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

Reeza — Mon, 01 Apr 2013 15:50:29 GMT

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 your loop;

%end;

*end of macro;

%mend;

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

Tom — Mon, 01 Apr 2013 15:55:50 GMT

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

So your code flow should look like:

do ....

end..

Do something with the summary table.

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

Xamius32 — Mon, 01 Apr 2013 16:12:45 GMT

this seems to be working. Thanks a bunch