Macro for dataset with multiple years

[AP718]

I have a set of 4 tables that are named the same as below and I need to pull the same data from each table for years: 2014-2017. How can I do this using a macro within a data step or with PROC SQL so that I am not re-writing the same code for each year? Thank you

Student_2014

Student_2015

Student_2016

Student_2017

PROC SQL;

CREATE TABLE COUNT_&YEAR

STUDENT_ZIP,

COUNT(DISTINCT STUDENT_ID) AS TOTAL_&YEAR

FROM

STUDENT_&YEAR

QUIT;

[Reeza · Accepted solution]

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

You're almost there, just wrap it in a macro now with the parameter. The tutorial above gives you step by step examples. I would recommend combining the data sets if they're reasonably sized though (less than a few million per) and use BY group processing instead. Both options are illustrated below. I fixed your SQL assuming I understand what you want correctly.

%macro count_data(year=);
PROC SQL;

CREATE TABLE COUNT_&YEAR as

select STUDENT_ZIP,

COUNT(DISTINCT STUDENT_ID) AS TOTAL_&YEAR

FROM

STUDENT_&YEAR

QUIT;

%mend count_data;

%count_data(year=2014);
%count_data(year=2015);
...

or instead use this:

data combined / view= combined;
set student_2014-student_2017 indsname=source;
dsn=source;
run;

proc sql;
create table summary as
select dsn, student_zip, count(distinct student_id) as total
from combined
group by dsn; /*edited in*/
quit;

[AP718]

Thank you! do I have to assign the years as it's not running in the Proc SQL version

%LET YEAR =

%PUT &YEAR;

[PaigeMiller]

If you do a GROUP BY in PROC SQL, you won't need macros at all.

[AP718]

the goal is to not have to write the same query multiple times to pull the same data from the 4 tables

[PaigeMiller]

---

@AP718 wrote:

the goal is to not have to write the same query multiple times to pull the same data from the 4 tables

---

Yes, you use the PROC SQL code from @Reeza but you add a GROUP BY ... and you get a solution with one query and no macros.

[Reeza]

I posted two, different, full working solutions based on what you posted. Did you run them exactly as is? If so, did they not work? If not, specify how exactly.

[Reeza]

---

@AP718 wrote:

Thank you! do I have to assign the years as it's not running in the Proc SQL version

%LET YEAR =

%PUT &YEAR;

---

No, assuming you're using the code I posted. If you wrote your own or changed it then I don't know, you'd have to post the full code. 

In general, make sure it works for one case, you have working code without macros, and then follow the tutorial and you should be fine. 

[AP718]

I had to fix a string and it worked. Thank you!

[PaigeMiller]

---

@AP718 wrote:

I have a set of 4 tables that are named the same as below and I need to pull the same data from each table for years: 2014-2017. How can I do this using a macro within a data step or with PROC SQL so that I am not re-writing the same code for each year? Thank you

Student_2014

Student_2015

Student_2016

Student_2017

PROC SQL;

CREATE TABLE COUNT_&YEAR

STUDENT_ZIP,

COUNT(DISTINCT STUDENT_ID) AS TOTAL_&YEAR

FROM

STUDENT_&YEAR

QUIT;

---

I wouldn't use a macro at all. I would append all the data sets, then do your analysis with GROUP BY year. That seems much simpler than trying to create a working macro here.