BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Xamius32
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

11 REPLIES 11
Tom
Super User Tom
Super User

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;

Xamius32
Calcite | Level 5

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"

Reeza
Super User

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.

Xamius32
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

Xamius32
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

Xamius32
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

Reeza
Super User

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;

Xamius32
Calcite | Level 5

this seems to be working. Thanks a bunch

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2232 views
  • 7 likes
  • 3 in conversation