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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
Astounding
PROC Star

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;

dyxdyx
Calcite | Level 5

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!

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
  • 2 replies
  • 6362 views
  • 0 likes
  • 2 in conversation