topic Re: Macro for data import in SAS Programming

Macro for data import

jmmedina25 — Mon, 03 Aug 2020 14:34:26 GMT

Hello, I'm trying to figure out how to automate this code so that I just need to change 1 number instead of copy and pasting code everytime I need to change it:

Where there is a '1' or a '2', that's the number that changes

*january;

PROC IMPORT OUT=pregnacy_referrals1

DATAFILE= "/windows/Pregnancy File 01.2020"

DBMS=XLSX REPLACE;

GETNAMES=YES;

SHEET="DETAILS";

RUN;

*february;

PROC IMPORT OUT=pregnacy_referrals2

DATAFILE= "/windows/Pregnancy File 02.2020"

DBMS=XLSX REPLACE;

GETNAMES=YES;

SHEET="DETAILS";

RUN;

Re: Macro for data import

PaigeMiller — Mon, 03 Aug 2020 14:44:05 GMT

As I think about this problem, do you want to determine the current month, which as of today would be month 8, and then run the code for the current month? Or can the month number be any number at any time of the year (example, it is August but you want the February data)?

Re: Macro for data import

ballardw — Mon, 03 Aug 2020 14:43:55 GMT

@jmmedina25 wrote:

Hello, I'm trying to figure out how to automate this code so that I just need to change 1 number instead of copy and pasting code everytime I need to change it:

Where there is a '1' or a '2', that's the number that changes

*january;

PROC IMPORT OUT=pregnacy_referrals1

DATAFILE= "/windows/Pregnancy File 01.2020"

DBMS=XLSX REPLACE;

GETNAMES=YES;

SHEET="DETAILS";

RUN;

*february;

PROC IMPORT OUT=pregnacy_referrals2

DATAFILE= "/windows/Pregnancy File 02.2020"

DBMS=XLSX REPLACE;

GETNAMES=YES;

SHEET="DETAILS";

RUN;

You probably want to rephrase your "Where there is a '1' or a '2', that's the number that changes"

because you have multiple 2's as in 2020.

And what about when 2021 rolls around??? You still have to change the 2020

Plus you probably should consider naming your sets pregnacy_referrals01 instead of pregnacy_referrals1. Other wise you would need two macro variables that still need to both be changed and not save any thing.

Consider:

%let monthnum=01;

PROC IMPORT OUT=pregnacy_referrals&monthnum.
DATAFILE= "/windows/Pregnancy File &monthnum..2020"
DBMS=XLSX REPLACE;
GETNAMES=YES;
SHEET="DETAILS";
RUN;

The 2 periods in is intentional and required. The macro processor uses . to indicate the end of a macro variable. If you use &monthnum.2020 the value would resolve to 012020, without a . and not be your file name. And if you were to use &monthnum2020 you would get an error that the macro variable monthnum2020 is undefined.

Re: Macro for data import

jmmedina25 — Mon, 03 Aug 2020 15:57:57 GMT

Okay, thanks. Since I need the code to go through multiple months at one time, can I do %let monthnum=01, 02, 03, ect.?

Re: Macro for data import

jmmedina25 — Mon, 03 Aug 2020 15:59:25 GMT

Ideally, it would iterate through the months starting at 01.2020 and ending on the current month 08.2020

Re: Macro for data import

Quentin — Mon, 03 Aug 2020 16:06:04 GMT

No, because then your code would resolve to :

PROC IMPORT OUT=pregnacy_referrals01,02,03
DATAFILE= "/windows/Pregnancy File 01,02,03.2020"
DBMS=XLSX REPLACE;
GETNAMES=YES;
SHEET="DETAILS";
RUN;

If you want to pass a list of values, one way would be to write a macro with an iterative %DO loop that iterates over the list, and generates one PROC IMPORT step for each item of the list. There are also non-macro approaches.

Since your subject asks about macro, have you tried the macro approach? If so, please share your macro, I'm sure folks will be happy to point out why it's not working. While people might also just write the macro for you, IMHO you learn more when you share code that people can respond to.

Re: Macro for data import

PaigeMiller — Mon, 03 Aug 2020 17:15:38 GMT

%macro iterate;
data _null_;
    call symputx('curr_month',month(today()));
    call symputx('year',year(today()));
run;
%do i=1 %to &curr_month;
    PROC IMPORT OUT=pregnacy_referrals_&year._%sysfunc(putn(&i,z2.))
        DATAFILE= "/windows/Pregnancy File %sysfunc(putn(&i,z2.)).&year"
    DBMS=XLSX REPLACE;
    GETNAMES=YES;
    SHEET="DETAILS";
    RUN;
%end;
%mend iterate;
%iterate

As a suggestion for improvement, if people are naming Excel file as 01.2020, this will not sort in proper chronological order. If you name the Excel files as 2020.01, then these indeed will sort in proper chronological order.

Also as a fine point to help you obtain better answers more quickly, the concept of iteration was not mentioned in your original post, you probably would have been better off to say that specifically.