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?
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;
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;
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"
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.
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.
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.
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.
So it sounds like you placed after the end of the loop instead of inside the loop.
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.
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.
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;
this seems to be working. Thanks a bunch
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.