import a serious of .xlsx files into SAS

[France]

Dear all,

How can I import a serious of files which named from 'Export_Pat_name_with_address_dis1' to 'Export_Pat_name_with_address_dis56' by using one code.

I can import each of them by 

proc import datafile = 'C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis1.xlsx' 
OUT=step5.Export_Pat_name_with_address_dis1 DBMS=XLSX replace;

Sheet='Page 1';
RANGE='A1:F1001';
GETNAMES=YES;

RUN;

Each of them can use the same information like

Sheet='Page 1';
RANGE='A1:F1001';
GETNAMES=YES;

Could you please give me some suggestion about this.

Thanks in advance

[r_behata]

You may use a Macro to accomplish this. Hope this helps. Note that the sas dataset name cannot be longer than 32 characters.

%macro loopit;

%do i=1 %to 56;
proc import datafile = 'C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis&i..xlsx' 
OUT=step5.Export_Pat_name_with_&i. DBMS=XLSX replace;

Sheet='Page 1';
RANGE='A1:F1001';
GETNAMES=YES;

RUN;
%end;
%mend;

%loopit

[France]

Hello behata,

thank you for your suggestion.

however, I get the result like 

27   %macro loopit;
28
29   %do i=1 %to 56;
30   proc import datafile =
30 ! 'C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis&i..xlsx'
31   OUT=step5.Export_Pat_name_with_&i. DBMS=XLSX replace;
32
33   Sheet='Page 1';
34   RANGE='A1:F1001';
35   GETNAMES=YES;
36
37   RUN;
38   %end;
39   %mend;
40
41   %loopit

ERROR: Physical file does not exist,
       C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis&i..xlsx.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.39 seconds
      cpu time            0.03 seconds

I can use the following code to import each of them. like 

42   proc import datafile =
42 ! 'C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis1.xlsx'
43   OUT=step5.Export_Pat_name_with_1 DBMS=XLSX replace;
44
45   Sheet='Page 1';
46   RANGE='A1:F1001';
47   GETNAMES=YES;
48
49   RUN;

NOTE:    Variable Name Change.  Company name -> Company_name
NOTE:    Variable Name Change.  National ID -> National_ID
NOTE:    Variable Name Change.  Matched BvD ID -> Matched_BvD_ID
NOTE:    Variable Name Change.  Matched company name -> Matched_company_name
NOTE: The import data set has 1000 observations and 6 variables.
NOTE: STEP5.EXPORT_PAT_NAME_WITH_1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.48 seconds
      cpu time            0.04 seconds

could you please give me more advise?

[r_behata · Accepted solution]

Sorry, my bad  the path was in single quotes . Now, try this 

%macro loopit;

%do i=1 %to 56;
proc import datafile = "C:\Users\70660\Desktop\Datastream_original\Export_Pat_name_with_address_dis&i..xlsx"
OUT=step5.Export_Pat_name_with_&i. DBMS=XLSX replace;

Sheet='Page 1';
RANGE='A1:F1001';
GETNAMES=YES;

RUN;
%end;
%mend;

%loopit