topic Re: Using macro in proc import with file name ending with a date in New SAS User

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

Didi_b — Thu, 03 Feb 2022 16:39:43 GMT

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;

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

Didi_b — Thu, 03 Feb 2022 16:40:46 GMT

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;

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

PaigeMiller — Thu, 03 Feb 2022 16:56:07 GMT

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.

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

PaigeMiller — Thu, 03 Feb 2022 16:59:22 GMT

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.

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

ballardw — Thu, 03 Feb 2022 17:07:47 GMT

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.

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

Didi_b — Thu, 03 Feb 2022 17:13:27 GMT

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

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

Didi_b — Thu, 03 Feb 2022 17:20:18 GMT

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;

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

PaigeMiller — Thu, 03 Feb 2022 17:25:35 GMT

I think the answer I gave above identifies the problem.

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

Reeza — Thu, 03 Feb 2022 17:29:34 GMT

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;

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

Didi_b — Thu, 03 Feb 2022 17:33:51 GMT

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

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

Didi_b — Thu, 03 Feb 2022 17:37:21 GMT

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

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

PaigeMiller — Thu, 03 Feb 2022 17:40:16 GMT

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

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

Reeza — Thu, 03 Feb 2022 17:40:56 GMT

Depends on validvarname option?

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

Kurt_Bremser — Thu, 03 Feb 2022 17:43:15 GMT

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

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

Kurt_Bremser — Thu, 03 Feb 2022 17:45:04 GMT

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

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

Reeza — Thu, 03 Feb 2022 18:00:06 GMT

True...

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

Tom — Thu, 03 Feb 2022 18:15:38 GMT

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.

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

ballardw — Thu, 03 Feb 2022 18:36:01 GMT

@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

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

Didi_b — Thu, 03 Feb 2022 19:46:49 GMT

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

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

Reeza — Thu, 03 Feb 2022 19:54:49 GMT

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.