IF/THEN/ELSE Proc Import Macro

[ImASasMan]

Hello!

So I have been trying to create a macro to avoid my proc import from erroring out if the file does not exist. It is based upon the date that the user inputs prior to running the file. Here is a bit of code for reference.

%macro y4import;

%If floor(&end_date./100) >= &Year4. %then %do;

PROC IMPORT OUT = WORK.Rates_&YEAR4._H1 DATAFILE= YR4H2 DBMS=xlsx REPLACE;

SHEET="SAS Prep";

GETNAMES=YES;

RUN;

%end ;

%else %end;

%mend y4import;

%y4import

Year4 and End_date are both variables that are set previously. End_date is manually put in by the user and Year4 is a macro that takes the year from the beg_date set by the user and steps it forward 3 years. End_date is an integer in YYYYMM format, thus why the floor function is used. When I run the code above, nothing happens, even though I have manipulated the variables to ensure that the %If statement is true.

To summarize, I am trying to achieve that if the end_date for data to be imported is not equal to or greater than year4, then don't import the file.

Any insight on this, or if this is even possible would be greatly appreciated.

Thanks!

[PaigeMiller]

Are there error messages in the log? Can you show them to us?

Please re-run the program with this as the first line

options mprint symbolgen mlogic;

and then show us the log.

In particular, this won't work

%If floor(&end_date./100) >= &Year4. %then %do;

because floor will not work with %IF. You could use

%If %sysfunc(floor(&end_date./100)) >= &Year4. %then %do;

because FLOOR isn't recognized by  the macro processor unless it is inside %SYSFUNC 

Whether there are other errors, I don't know. The log ought to make clear what the problems are.

[ImASasMan]

Prior to adding the %sysfunc and options lines, there were no errors or warnings to report. After adding them, nothing occurred, and there were still no errors or warnings to report.

[PaigeMiller]

The next step in debugging is to actually look at the code produced by this macro and see if the values of the macro variables are what you expect. So again, I ask to see the log.

[Tom]

You macro is referencing two macro variables , END_DATE and YEAR4, that are NOT defined as inputs to the macro.  Where are those macro variables defined? What values do they have?  

What is the test that you want to perform?  Right now you are testing if the lowercase letter f is larger than the first character in the macro variable YEAR4.  If YEAR4 has something like 2020 then the digit 2 is always smaller than any letter.

Does you macro even compile?  You have a %END without any corresponding %DO.

Since you didn't define any input parameters to the macro you might want to add a semi-colon after the macro call to make sure that SAS knows you are done typing the name of the macro.

[ImASasMan]

These macros are defined earlier in the code and are referenced throughout the sheet in various file paths, names, etc. Do they need to be referenced directly in the macro?

End_Date is defined as 202001 and Year4 is defined as 2020.

I want to test that the first 4 digits of end_date are equal to or greater than the value in Year4.

[Reeza]

Untested.

FEXIST() to check for file exist and using %SYSFUNC() to wrap functions in a macro. 

FYI - I would highly recommend redesigning this a bit to use parameters rather than assume a macro variable exists. Its a helpful design feature to ensure your code is using the correct parameters and makes your process more efficient in the long run. I'll assume you're actually incorporating this into another program, not calling it in the fashion you are here though. 

%let end_date = 202001;
%let year4 = 2020;

%macro y4import;

%If %sysfunc(floor(&end_date./100)) >= &Year4. and %sysfunc(fexist(yr4h2)) %then %do;

 

PROC IMPORT OUT = WORK.Rates_&YEAR4._H1 DATAFILE= YR4H2 DBMS=xlsx REPLACE;

SHEET="SAS Prep";

 

GETNAMES=YES;

RUN;

%end ;



%mend y4import;

 

%y4import

[Tom]

---

@ImASasMan wrote:

These macros are defined earlier in the code and are referenced throughout the sheet in various file paths, names, etc. Do they need to be referenced directly in the macro?

End_Date is defined as 202001 and Year4 is defined as 2020.

I want to test that the first 4 digits of end_date are equal to or greater than the value in Year4.

---

So you want to compare the first 4 characters of END_DATE to YEAR4?

%if "%substr(&end_date,1,4)"="&year4" %then ...

[Reeza · Accepted solution]

It seems like you're asking two questions here:

1. What is wrong with my current code?

2. How to check if a file exists and call this macro conditionally. 

The answer to #2 is here, basically use the FILEEXIST() function and then add an %IF/%THEN based on that logic. In this example it deletes the file using FDELETE but you'll want to run your IMPORT instead.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n108rtvqj4uf7tn13tact6ggb6uf.htm&docse...

Regarding #1, as others have indicated you'll have to post the log with the debugging options for us to tell what's wrong. We can't see the code you actually submit or what the macro variables resolve to, so we can only comment on what you've posted which doesn't indicate where the error may be. 

Good Luck. 

[ballardw]

Are any of these XLSX files supposed to have similar structure and content? This means the same number of variables, with the same names and each variable having the same properties such as type (numeric or character) length and possibly date values?

If so I would suggest manually importing two of them and then run proc contents on those two resulting data sets. See if the variables, especially character, have the same properties.

If not then Proc Import for many files is going to lead to many steps to "fix" them.

I might suggest using PCFiLES and reading them into a standard structured data set.

[ImASasMan]

Thank you everyone for the replies. The reason I was having such an issue with this was because I needed to restart my SAS. The state it was in when I posted this was not erroring out and giving me proper insight. The simple solution was to add the %sysfunc in front of floor and add a %do in the %else statement. Here is what I ended up using:

%macro y4H1import;

%LET MONTH = (%EVAL(&END_DATE. - %EVAL(&YEAR4.*100)));

%If %SYSFUNC(floor(&end_date./100)) >= &Year4. AND &MONTH < 7 %then %do;

PROC IMPORT OUT = WORK.Rates_&YEAR4._H1 DATAFILE= YR4H1 DBMS=xlsx REPLACE;

SHEET="SAS Prep";

GETNAMES=YES;

RUN;

%end ;

%ELSE %DO;

%PUT THE FILE DOES NOT EXIST;

%END;

%mend y4H1import;

%y4H1import

[Tom]

There is no need to nest the %EVAL() function calls. Once you are asking the macro processor to evaluate an integer arithmetic expression it can do the whole expression.  Do you really want the MONTH macro variable to have the parentheses in it?  

%LET MONTH = %EVAL(&END_DATE. - (&YEAR4.*100));

But since the string is in YYYYMM format to get the MM part out you can  just use %SUBSTR() function.

%let month=%substr(&end_date,5)

You seem to have changed the logic of your test by introducing this test of the month value.

Now you appear to be testing whether the YYYYMM string in END_DATE is for any year that is equal to or larger than the YYYY string in YEAR4 but also within the first half of the year.  So if YEAR4 is 2019 then values like 201903, 202005, 203006 will be valid.  But values like 201908, 201801, 202012 are not.

Or did you just want to test if it is in the first half of the year that YEAR4 represents?

%If &year4.01 <= &end_date <= &year4.06 %then %do;

[Reeza]

Given your initial question:
"So I have been trying to create a macro to avoid my proc import from erroring out if the file does not exist"

Your answer above does not do what you initially asked. It checks the month/date but not if a file exists. If that's what you needed, great.

[ImASasMan]

Thank you guys for the replies. As I'm sure you can tell, I am new to SAS, only been using it for about a month now. After posting again this morning and getting your comments back I found that my logic was in fact flawed and should not based upon the month and year values, but rather if the file itself exists. I proceeded to use the FILEEXIST function as was suggested yesterday. The code is as follows and ran properly without erroring out.

%macro check1;

%if %sysfunc(fileexist(&FILE_CHECK1.)) ge 1 %then %do;

PROC IMPORT OUT = WORK.Rates_&YEAR4._H1 DATAFILE= YR4H1 DBMS=xlsx REPLACE;

SHEET="SAS Prep";

GETNAMES=YES;

RUN;

%end;

%else %put

***************************

The file &FILE_CHECK2. does not exist

****************************;

%mend check1;

%check1

One mistake that I made along the way is that I tried to use the FILENAME that I had established for the import statement as a callable variable, which obviously is incorrect. I had to establish a new variable that added .xlsx onto the file path. Any other suggestions for improvements are greatly appreciated.