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
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
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
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.