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: 8,589

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
Super User
Super User
Posts: 8,620

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