Desktop productivity for business analysts and programmers

Using DO LOOP to import excel files

Occasional Contributor
Posts: 5

Using DO LOOP to import excel files

Hi all,


I have been trying below codes to import excel files using do loop and there is no error shown in the log. However, there is no output after I have tried to run below script. Anyone can highlight any error/issues with the below script?


%let s_folder = \\\Users\88094701\billsinstats;


%macro extract_playerbillin;


     data _null_;

           startdt = '1Apr2017'd;

           enddt = '30Apr2017'd;

         call symput('startdt', startdt);

         call symput('enddt', enddt);



%do dt=&startdt %to &enddt;


     data _null_;

           datadate = &dt;

           filedate = put(datadate, yymmddn8.);

         call symput('filedate', put(datadate, yymmddn8.));

           call symput('filepath',"&s_folder\igt_bill_in__&filedate..xlsx");

           put filedate ;



     %if %sysfunc(fileexist("&filepath")) %then %do;

           data _abs_player_billsin;

                attrib Date length=8 format=datetime21.2;

                attrib Machine length=$10 format=$char10.;

                attrib Location length=$10 format=$char10.;

                attrib EventCode length=$20 format=$char20.;

                attrib Event length=$50 format=$char50.;

                attrib ‘Bonus ID’n length=$10 format=$char10.;

                attrib Amount length=8 format=best12.;

                attrib Staff length=$50 format=$char50.;

                attrib ‘ID #’n length=8 format=best20.;

                attrib ‘Insert Date’n length=8 format=datetime21.2;


                infile "&filepath"

                           delimiter = ','






                keep Date Machine Location;



           %if &dt=&startdt %then %do;

                data abs_player_billsin;

                     set _abs_player_billsin (obs=0);




           proc append

                data = _abs_player_billsin

                base = abs_player_billsin;



           proc datasets lib=work nolist nowarn;

                delete     _abs_player_billsin;





%mend extract_playerbillin;



Super User
Posts: 9,547

Re: Using DO LOOP to import excel files

Posted in reply to vannesslee

You can't read a true xlsx file with a data step. XLSX files are zip-compressed XML.

Also use options mprint mlogic to show what the macro is really doing.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,193

Re: Using DO LOOP to import excel files

Posted in reply to vannesslee

Datastep to create the loop, call execute to generate one proc import and either a dataset set or proc append for each value in the given range:

data _null_;
  do dt='01apr2017'd to '30apr2017'd;
    call execute('proc import datafile="'||cats(&filepath.,&filename.,put(dt,date9.),".xlsx")||'" out=tmp replace; run;');
if _n_=1 then call execute('data abs_player_billsin; set tmp; run;');
else call execute('proc append base=abs_player_billsin data=tmp; run;')
end; run;


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation