Hi all,
SAS 9.4
I 'm trying to create a loop so that all the xlsx-files are imported. I notice that the code below is wrong but I don't know how to correct it the right way. I have 4 excelfiles to import. The result of this code is that the first excelfile is imported 4 times (and it overwrites itself each time). The result should be that 4 each excel file is imported once. Each excelfile has a different variable in its filename (ERK = 009, 090,091,777). Only the 009 is imported. Could anyone help me with this please?
/*macro to import a single excel*/
%macro import(input);
OPTIONS VALIDVARNAME=ANY;
PROC IMPORT OUT=work.B1B2_E&erk. (keep = N_BUD_AN ID_AGR TIT_N Source N2018)
DATAFILE= "&path\&input..xlsx"
DBMS=excelcs REPLACE;
RANGE="B1B2$"; /*to import the sheet completely*/
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
%mend;
/*loop to import all the files*/
%macro Loadall;
%do i=1 %to &numobs;
%import(&&filein&i);
%end;
%mend;
%loadall;
Since you do not change &erk in the loop (or derive it somehow from &input), the dataset is always overwritten with the data from the latest import.
How do I write the code?
Before the macro this is written.
/*Each Excel file gets a variable, number*/
data _null_;
set mydata.deelnemerslijst end=endobs;/*end=variable --> at the last observation it starts counting*/
call symputx('fileIN'!!left(_n_),excel_file);/*takes the name and puts a number in front "n" (_n_ counts the lines --> this combination is named 'fileIN'*/
if endobs=1 then call symputx('numobs',_N_);
run;
You need to make up a rule for building the macro variable erk.
There are many examples of this type on here, please use the search functionality:
https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...
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 16. 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.