Macro for data import

[jmmedina25]

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;

[PaigeMiller]

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)?

[jmmedina25]

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

[PaigeMiller]

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

[ballardw]

---

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

[jmmedina25]

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

[Quentin]

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.