Solved: Re: Using macro in proc import with file name ending with a date

Didi_b · Posted 02-03-2022 11:39 AM

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;

Reeza · Posted 02-03-2022 12:29 PM

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;

View solution in original post

Didi_b · Posted 02-03-2022 11:40 AM

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 · Posted 02-03-2022 11:56 AM

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.

--
Paige Miller

Didi_b · Posted 02-03-2022 12:13 PM

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 · Posted 02-03-2022 12:20 PM

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 · Posted 02-03-2022 12:25 PM

I think the answer I gave above identifies the problem.

--
Paige Miller

PaigeMiller · Posted 02-03-2022 11:59 AM

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.

--
Paige Miller

Didi_b · Posted 02-03-2022 12:37 PM

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 · Posted 02-03-2022 12:40 PM

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

--
Paige Miller

Reeza · Posted 02-03-2022 12:40 PM

Depends on validvarname option?

Kurt_Bremser · Posted 02-03-2022 12:45 PM

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

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Reeza · Posted 02-03-2022 01:00 PM

True...

Kurt_Bremser · Posted 02-03-2022 12:43 PM

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

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

ballardw · Posted 02-03-2022 12:07 PM

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 · Posted 02-03-2022 12:33 PM

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