topic Re: Macro for dataset with multiple years in SAS Programming

Macro for dataset with multiple years

AP718 — Tue, 14 Jan 2020 16:24:53 GMT

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;

Re: Macro for dataset with multiple years

Reeza — Tue, 14 Jan 2020 18:31:45 GMT

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-Appendix/ta-p/291716

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;

Re: Macro for dataset with multiple years

PaigeMiller — Tue, 14 Jan 2020 17:40:12 GMT

@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.

Re: Macro for dataset with multiple years

AP718 — Tue, 14 Jan 2020 17:42:17 GMT

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

%LET YEAR =

%PUT &YEAR;

Re: Macro for dataset with multiple years

PaigeMiller — Tue, 14 Jan 2020 17:44:05 GMT

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

Re: Macro for dataset with multiple years

Reeza — Tue, 14 Jan 2020 18:07:24 GMT

@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.

Re: Macro for dataset with multiple years

AP718 — Tue, 14 Jan 2020 18:07:57 GMT

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

Re: Macro for dataset with multiple years

PaigeMiller — Tue, 14 Jan 2020 18:17:22 GMT

@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.

Re: Macro for dataset with multiple years

Reeza — Tue, 14 Jan 2020 18:20:41 GMT

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.

Re: Macro for dataset with multiple years

AP718 — Tue, 14 Jan 2020 18:42:25 GMT

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