topic Re: proc sql in a %Do loop in SAS Programming

proc sql in a %Do loop

dyxdyx — Fri, 24 Mar 2017 17:37:31 GMT

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;

Re: proc sql in a %Do loop

Astounding — Fri, 24 Mar 2017 18:23:19 GMT

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;

Re: proc sql in a %Do loop

dyxdyx — Fri, 24 Mar 2017 18:31:52 GMT

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!