BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

Hi,

suppose that I have 20 excel files file1 ... file20.

To import an individual file, say file 1, I would do the following:

PROC IMPORT OUT= file1 

            DATAFILE= " K:\FilesFormatted\file1.xlsx"

            DBMS= EXCEL REPLACE;

    sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

(Please note that I want the imported sas data to have the same name as the original excel file).

Is it possible to write a code, perhaps a macro, that will import all the files file1-file20 instead of repeating the above code for each individual excel file?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

As Reeza pointed out . Your CALL EXECUTE statement is not right .

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

  call execute(cats('%import_myfile(i=',filenum,')'));

run;

Xia Keshan

View solution in original post

9 REPLIES 9
jakarman
Barite | Level 11

Gues what, you forgot you are on a guessing  approach.  (always something to expect to go wrong). No other approach acceptable with reliable data definitions?

---->-- ja karman --<-----
Ksharp
Super User

Make a table to contain these file names then CALL EXECUTE()  to execute a macro of proc import .

ilikesas
Barite | Level 11

Hi Xia,

I searched on the internet (including the sas site) and this is what I could put together:

%macro import_myfile(i=);

proc import file="C:\Documents and Settings\HP_Administrator\Desktop\file\file&i" out=file&i.

DBMS= EXCEL REPLACE;

   sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

%mend import_myfile;

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

   call execute(%import_myfile(filenum));

run;


But unfortunately I got an error message...


Could you please help me to figure out what is wrong with the above code?


Thank you

Reeza
Super User

What's your error?

Your call execute is wrong as well, it expects a string not macro code, something like below, untested

call execute (catt('%import_myfile(', filename, ');'));

ilikesas
Barite | Level 11

Hi,

it says that more positional parameters found than defined and that the execute doesn't have enough arguments.

Also, I redid  the call execute in string form :

data _null_;

   set data_torun;

   call execute('%import_myfile(filenum)');

run;


but stll the same problem

ilikesas
Barite | Level 11

I think that I actually managed to do it:

%macro import_myfile(howmany );

/*1. import the files*/

   %do i=1 %to &howmany;

proc import file="C:\Documents and Settings\HP_Administrator\Desktop\file\file&i" out=file&i.

DBMS= EXCEL REPLACE;

   sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

%end;

/*2.put them all into on table*/

data file;

set file1-file&howmany;

run;

/*3.delete the individual files*/

%do n = 1 %to &howmany;

proc sql;

drop table file&n;

quit;

%end;

%mend import_myfile;

%import_myfile(howmany=3);



The only thing is that for each individual file name (say table1 ... table20 instead of the present file1...file20) I need to change the macro itself. I tried to do %macro import_myfile(howmany, name) but got lots of errors when inside the actual macro changes the file to &name. Could you please help me?


Thank you

Ksharp
Super User

As Reeza pointed out . Your CALL EXECUTE statement is not right .

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

  call execute(cats('%import_myfile(i=',filenum,')'));

run;

Xia Keshan

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Save your data to a proper data file format, for instance CSV.  Write a datastep which imports those CSV, you can use wildcards (*), plenty of topics on here about it:

PaigeMiller
Diamond | Level 26

The %FOR macro does this, it doesn't require that you know the names of the Excel files in advance. See the final example at:

http://www.sascommunity.org/wiki/Streamlining_Data-Driven_SAS_With_The_%25FOR_Macro

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 2059 views
  • 3 likes
  • 6 in conversation