Using macro in proc import with file name ending with a date

[ballardw]

---

@Didi_b wrote:

Sure it is a xls files
I copy past the file name.

When I proc import one file whitout macro it works. Since I have a lot of them I need to use macro.

Thanks I'll look for what you suggested in forum

---

By default SAS will not allow data sets to have - characters in the names. SAS data set names start with _ or a letter and contain _, letters and digits only by default. So either replace the - with _ characters or live with using name literals setting the OPTION VALIDMENAME=EXTEND; AND then having to write "Something-dash"N name literals to reference data sets.

So the OUT= is attempting to create an invalid SAS data set name.

proc import datafile=refile dbms=xls out=&T01. replace;

Strongly suggest replacing the - characters with _.

It takes ONE line of code to replace the - with _ as shown below:

%macro dummy (to);
%let to = %sysfunc(translate(&to.,_,-));
%put to is now: &to.;
%mend;

%dummy(filename_2021-01-23)

Which will show in the log:

to is now: filename_2021_01_23

[Didi_b]

Hello Ballardw I didn't get to try your solution yet. But, I'll try and let you know if it works too. Thanks a lot for your support

[Tom]

You can add more parameters to the macro to give the user more control.

Try this:

%macro import(filename,member);
%if 0=%length(&member) %then %let member=%sysfunc(compress(&filename,. -));

proc import
  datafile="filepath\&filename..xls"
  dbms=xls 
  out=&member. replace
;
  sheet="sheetname";
  namerow=7;
  startrow=10;
  getnames=yes;
run;
%mend import;

%import(filename_2022-01-27);
%import(filename_2022-01-31,filename_2022_01_31);

As to your other question:

*Endrow=934 (the end rows are not the same for each table how could I fix that;

You need to explain more what that means.  Is the problem that there are "footers" in the Excel sheets that are not data? What problems do they cause? 

Are they easy to detect and delete after the data is imported?  In which case just add a where= dataset option to the OUT= dataset to eliminate those observations.

Or does including them cause changes to the data you do want?  In that case read the file twice. Check the first version and figure out what value to use for the ENDROW= statement and put it into a macro variable you can use to re-import the file with that setting.

[Didi_b]

@Tom I'm having the same error I had with Reeza solution when I tried to apply your solution.

I'll try to fix the path. I hope I'll be able to do it.

https://communities.sas.com/t5/SAS-Studio/Proc-Import-with-Macro-Variable/td-p/425532

Here the reference I used.

I'll will try your solution for the end of the Row.

To respond to your question : it is yes I have foot not at the end of each Excel file and the line number is not the same. I have something like this

*** Fin du rapport Call_Global_2022-01-31.xls ***

I let you know if I manage to find a solution. Thanks for the support. I really appreciate it

[Reeza]

That example has some key differences - for one the entire file name is passed as part of the macro and you're only passing a part of it. 

Second the names there are valid SAS names, yours are not. 

Following the methodology in the second link will help you convert your working program to a macro. 

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

[Didi_b]

Hello Reeza, thanks a lot, I will try them too definetely.

[Tom]

So does that text appear in the first column of the sheet? So the first variable in the resulting SAS dataset?

Does the rest of that column in the sheet contain numbers? Or is it already text?

If it is already text then why not just read that observations in and delete it.

To test it use PROC IMPORT twice to generate two different SAS datasets. One time without telling where to stop.  Then run PROC COMPARE to compare the two datasets.  Are there any differences in how the variables are defined (other than perhaps the LENGTH of the first variable)?  Are there any differences in the values on the corresponding observations?

[Didi_b]

Hello,

The text appear in the last row of the sheet but it merges all the culoms in that last row. However, it appear only in the last colum in SAS.

So I test importing with and without the text and it doesnt change the data. Therefor, I will apply your solution to import with the text and delete it after.

Please accept my sincere appreciation for your all your solutions and guidance. Thank you very much.

[Didi_b]

Hello @Tom your solution works too but I don't see the tag to accept it as a solution.

It's like when I accept the other one from Reeza I can't see other tags "accep as a solution".

If someone can show me beacuse I'd like to accept it as a solution too.

[Reeza · Accepted solution]

Please put your code in code blocks.

Try using COMPRESS() to remove the - and spaces from file names. You need to create valid data set names for the data sets. 

If this doesn't work, make sure to post the log.

options mprint symbolgen;
%macro import(T01);

FILENAME REFFILE "filepath\&T01..xls";

proc import datafile=refile dbms=xls out=%sysfunc(compress(&T01. , "- ") replace;
sheet="sheetname";
namerow=7;
startrow=10;
*Endrow=934 (the end rows are not the same for each table how could I fix that;
getnames=yes;
run;
%mend import;

%import (filename_2022-01-27);
%import (filename_2022-01-31);

---

@Didi_b wrote:

I would like to import multiple Excel file into SAS using macro. My programm didn't work because my files names are ending with dates. Also my line endrow is different for each excel file. I would like to get some help to succeed please. Excel file name are the same in different excel file, the difference is on date. The sheet name in each Excel file are the same too as the rest except the Endrow. I can't change the Excel files name because there are a lot of them.

here my program :

%macro import(R01);
FILENAME REFFILE "filepath\&T01..xls";

proc import datafile=refile dbms=xls out=&T01. replace;
sheet="sheetname";
namerow=7;
startrow=10;
*Endrow=934 (the end rows are not the same for each table how could I fix that;
getnames=yes;
run;
%mend import;

%import (filename_2022-01-27);
%import (filename_2022-01-31);
/*etc.*/
/*the table name with -01-27, etc. not working*/
run;

---

[Didi_b]

/**here my separate proc import that works fine*/

proc import datafile="Z:\x\_Stat\T01\Call_Global_2022-01-27.xls" dbms=xls out=table1 replace;
sheet="FileGroup";
namerow=7;
startrow=10;
Endrow=943;
getnames=yes;
run;

proc import datafile="Z:\x\_Stat\T01\Call_Global_2022-01-31.xls" dbms=xls out=table2 replace;
sheet="FileGroup";
namerow=7;
startrow=10;
*Endrow=958;
getnames=yes;
run;

/****Here my macro import that doesn't work beacuse of the date in file name with '-'**/

%macro import(T01);

FILENAME REFFILE "Z:\x\_Stat\T01\&T01..xls";

PROC IMPORT DATAFILE=REFFILE DBMS=XLS OUT=&T01. REPLACE; 
GETNAMES=YES;
RUN;
%mend import;

%import (Call_Global_2022-01-27);
%import (Call_Global_2022-01-27);
/*etc.*/
run;
/********************/

/**Reeza, Here your solution I applied** I add one parenthes that was missing and the run statement*/

options mprint symbolgen;
%macro import(T01);

FILENAME REFFILE "Z:\x\_Stat\&T01..xls";

proc import datafile="Z:\x\_Stat\&T01..xls" dbms=xls out=%sysfunc(compress(&T01. , "- ")) replace;
sheet="FileGroup";
namerow=7;
startrow=10;
getnames=yes;
run;
%mend import;

%import (filename_2022-01-27);
%import (filename_2022-01-31);
run;

/*Here the log result when I apply your solution*/

1    options mprint symbolgen;
2    %macro import(T01);
3
4    FILENAME REFFILE "Z:\x\_Stat\&T01..xls";
5
6    proc import datafile="Z:\x\_Stat\&T01..xls" dbms=xls out=%sysfunc(compress(&T01. , "-
6  !  ")) replace;
7    sheet="FileGroup";
8    namerow=7;
9    startrow=10;
10   getnames=yes;
11   run;
12   %mend import;
13
14   %import (filename_2022-01-27);
SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-27
MPRINT(IMPORT):   FILENAME REFFILE "Z:\x\_Stat\filename_2022-01-27.xls";
SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-27
SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-27
MPRINT(IMPORT):   proc import datafile="Z:\x\_Stat\filename_2022-01-27.xls" dbms=xls
out=filename_20220127 replace;
MPRINT(IMPORT):   RXCL;
MPRINT(IMPORT):   sheet="FileGroup";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;

ERROR: Physical file does not exist, Z:\x\_Stat\filename_2022-01-27.xls.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.08 seconds
      cpu time            0.03 seconds

15   %import (filename_2022-01-31);
SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-31
MPRINT(IMPORT):   FILENAME REFFILE "Z:\x\_Stat\filename_2022-01-31.xls";

SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-31
SYMBOLGEN:  Macro variable T01 resolves to filename_2022-01-31
MPRINT(IMPORT):   proc import datafile="Z:\x\_Stat\filename_2022-01-31.xls" dbms=xls
out=filename_20220131 replace;
MPRINT(IMPORT):   RXCL;
MPRINT(IMPORT):   sheet="FileGroup";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;

ERROR: Physical file does not exist, Z:\x\_Stat\filename_2022-01-31.xls.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

16   run;

@Reeza I was making a copy of two Excel file to show you so you could help me better (in file path I put x in place of real name because I can't share it). I hope you could help with my new post. Thanks for taking the time to help me. I really appreciate it.

[Reeza]

So in your example code you have the OUT=table1. 

However in your original macro you have OUT=filename_2022-01-31

I added the COMPRESS function to fix that, which it seems to have done but you know have a different error:

ERROR: Physical file does not exist, Z:\x\_Stat\filename_2022-01-31.xls.

Looking at your code that works your path was along the lines of :

"Z:\x\_Stat\T01\Call_Global_2022-01-31.xls

You're missing the CALL_GLOBAL part of the file name it seems. 

Fix your path to the files in the macro and it should work fine. 

[Didi_b]

I'm trying to fix it. I'll let you know if I manage to do it.

FYI this is my reference to do my program

%macro import(industry);

FILENAME REFFILE '/folders/myfolders/Bachelorarbeit/Daten/&industry..xlsx';

PROC IMPORT DATAFILE=REFFILE DBMS=XLSX OUT=&industry. REPLACE; 
GETNAMES=YES;
RUN;
%mend import;

%import (aerodef);
%import (automobiles);
/*etc.*/
run;

https://communities.sas.com/t5/SAS-Studio/Proc-Import-with-Macro-Variable/td-p/425532 the link for the reference

[SASKiwi]

What OS does your SAS server run on? Your original program uses a Windows file path. Check with your SAS administrator regarding the correct path.

[Didi_b]

Sorry for responding in late. It runs on W32_10PRO.

I also figured it out the path problem. I made a mistake in my file path. 

It's all good now. Thanks for your support.