DATA Step, Macro, Functions and more

proc sql in a %Do loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

proc sql in a %Do loop

I am trying to pull data from 17 datasets, data2001, data2002, ...data2017 (one for each year from 2001 to 2017). I also need to run a proc sql procedure to each dataset. Can anyone tell me what is wrong with my code please? Many thanks!

 

 

%macro pull_data;
%do i = 2001 %to 2017;

  data tmp&i;
  set raw.data&i;
  run;

  proc sql;
     create table temp&i as
     select unique
     redcode, year(date) as year, qtr(date) as qtr, mean(price) as avr_price, count(price) as n
     from tmp&i
     group by redcode, year, qtr;
  quit;

%end;
%mend pull_data;

data full; set temp2001 - temp2017; run;


Accepted Solutions
Solution
‎03-24-2017 02:28 PM
Super User
Posts: 5,517

Re: proc sql in a %Do loop

You must have some reason to suspect that something is wrong. It would be helpful if you were to share that information.

 

At any rate, here are a few things to look at.

 

Your code defines a macro but never tries to execute it.  You need to insert this line of code before the final DATA step:

 

%pull_data

 

The DATA steps within the macro aren't really needed.  SQL can use your permanently saved SAS data sets:

 

from raw.data&i

 

It is conceivable that your dates are actually datetime values instead of date values, so the YEAR and QTR functions return the wrong values.

 

It is conceivable (SQL isn't my strongest area) that the GROUP BY clause needs to tell SQL that some fields are not to be found within the incoming data:

 

group by redcode, calculated year, calculated qtr;

View solution in original post


All Replies
Solution
‎03-24-2017 02:28 PM
Super User
Posts: 5,517

Re: proc sql in a %Do loop

You must have some reason to suspect that something is wrong. It would be helpful if you were to share that information.

 

At any rate, here are a few things to look at.

 

Your code defines a macro but never tries to execute it.  You need to insert this line of code before the final DATA step:

 

%pull_data

 

The DATA steps within the macro aren't really needed.  SQL can use your permanently saved SAS data sets:

 

from raw.data&i

 

It is conceivable that your dates are actually datetime values instead of date values, so the YEAR and QTR functions return the wrong values.

 

It is conceivable (SQL isn't my strongest area) that the GROUP BY clause needs to tell SQL that some fields are not to be found within the incoming data:

 

group by redcode, calculated year, calculated qtr;

Occasional Contributor
Posts: 7

Re: proc sql in a %Do loop

Posted in reply to Astounding

Astouding, thanks so much for the quick reply. You are spot on - I did not have the %pull_data line. Once I add it to the code, it seems to be working now. Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 517 views
  • 0 likes
  • 2 in conversation