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

[Didi_b]

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]

Correction :

%macro import(T01);
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;

[PaigeMiller]

When you have errors in the log, you need to show us the ENTIRE log for this macro. Do not show us tiny portions of the log, we need to see ALL of it for this macro.

You need to turn on the macro debugging options, by running the next command, and then run the code again.

options mprint;

Please copy the log as text and paste it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

[Didi_b]

1    /* Macro proc import*/
2    options mprint;
3    %macro import(T01);
4    FILENAME REFFILE "filepath\&T01..xls";
5
6    proc import datafile=refile dbms=xls out=&T01. replace;
7    sheet="sheetname";
8    namerow=7;
9    startrow=10;
10   *Endrow=934 (the end rows are not the same for each table;
11   getnames=yes;
12   run;
13   %mend import;
14
15   %import (filename_2022-01-27);
MPRINT(IMPORT):   FILENAME REFFILE
"filepath\filename_2022-01-27.xls";
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(IMPORT):   proc import datafile=refile dbms=xls out=filename_2022-01-27 replace
sheet="sheetname";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   *Endrow=934 (the end rows are not the same for each table;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;
NOTE: Line generated by the macro variable "T01".
1    filename_2022-01-27
                              ---
                              22
                              200
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

16   %import (filename_2022-01-31);
MPRINT(IMPORT):   FILENAME REFFILE
"filepath\filename_2022-01-31.xls";


NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(IMPORT):   proc import datafile=refile dbms=xls out=filename_2022-01-31 replace
sheet="sheetname";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   *Endrow=934 (the end rows are not the same for each table;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;
NOTE: Line generated by the macro variable "T01".
1    filename_2022-01-31
                              ---
                              22
                              200
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

17   /*etc.*/
18   /*the table name with -01-27, etc. that not work*/
19   run;

@PaigeMiller 

[Didi_b]

1    /* Macro proc import*/
2    options mprint;
3    %macro import(T01);
4    FILENAME REFFILE "filepath\&T01..xls";
5
6    proc import datafile=refile dbms=xls out=&T01. replace;
7    sheet="sheetname";
8    namerow=7;
9    startrow=10;
10   *Endrow=934 (the end rows are not the same for each table;
11   getnames=yes;
12   run;
13   %mend import;
14
15   %import (filename_2022-01-27);
MPRINT(IMPORT):   FILENAME REFFILE
"filepath\filename_2022-01-27.xls";
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(IMPORT):   proc import datafile=refile dbms=xls out=filename_2022-01-27 replace
sheet="sheetname";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   *Endrow=934 (the end rows are not the same for each table;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;
NOTE: Line generated by the macro variable "T01".
1                filename_2022-01-27
                              ---
                              22
                              200
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

16   %import (filename_2022-01-31);
MPRINT(IMPORT):   FILENAME REFFILE
"filepath\filename_2022-01-31.xls";


NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
MPRINT(IMPORT):   proc import datafile=refile dbms=xls out=filename_2022-01-31 replace
sheet="sheetname";
MPRINT(IMPORT):   namerow=7;
MPRINT(IMPORT):   startrow=10;
MPRINT(IMPORT):   *Endrow=934 (the end rows are not the same for each table;
MPRINT(IMPORT):   getnames=yes;
MPRINT(IMPORT):   run;
NOTE: Line generated by the macro variable "T01".
1                filename_2022-01-31
                              ---
                              22
                              200
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
              FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

17   /*etc.*/
18   /*the table name with -01-27, etc. that not work*/
19   run;

@

[PaigeMiller]

I think the answer I gave above identifies the problem.

[PaigeMiller]

It appears that your error is here:

out=&T01.

When you use macro variables, they are replaced by their value when you run the code. And whatever the replacement values are MUST create legal valid working code. So, the first run produces this:

out=filename_2022-01-27

Is this legal SAS code, or is there a problem here? Can a data set name have hyphens in it?

In order to improve your macro writing, you need to first (FIRST!) have legal valid working SAS code without macros and without macro variables. Had you done this, you would most likely have avoided this issue.

[Didi_b]

Yes it is a legal SAS code. I just replace the sensitive name with filename that's it.

I didn't touch the rest. When I proc import one Excel file without the macro it works fine. But I need the macro since I have a lot of Excel Files.

Thanks

[PaigeMiller]

---

@Didi_b wrote:

Yes it is a legal SAS code.

---

No it is not legal SAS code. Can data set names have hyphens in it?

[Reeza]

Depends on validvarname option?

[Kurt_Bremser]

Even with validmemname=extend, one would then need name literals.

[Reeza]

True...

[Kurt_Bremser]

Your dates contain hyphens, which are not allowed in dataset names. Remove them for the dataset name.

[ballardw]

Are you sure those are XLS files and not XLSX?

You may need to carefully check which character is separating the digits in that "date" portion. You might be coding with a simple "dash" character from the keyboard but some may have named the file with a different character that looks like a dash.

You might consider searching this forum for the approaches that search folders and use the actual file names to import.

Caution: If all of these files are supposed to have the same structure and you intend to combine them at any point you really should not rely on Proc Import as every single file gets examined and imported separately. Which often leads to variables of different lengths or types. Not only that only the first few rows are examined and if you have longer values at the end of the file the values could be truncated because the shorter ones were at the top of the file. This is especially a concern if some columns are sparsely populated.

[Didi_b]

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