Desktop productivity for business analysts and programmers

Using DO LOOP to import excel files

Reply
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 = \\10.64.78.49\Users\88094701\billsinstats;

 

%macro extract_playerbillin;

          

     data _null_;

           startdt = '1Apr2017'd;

           enddt = '30Apr2017'd;

         call symput('startdt', startdt);

         call symput('enddt', enddt);

     run;

 

%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 ;

     run;

 

     %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 = ','

                           dsd

                           truncover

                           firstobs=2

                           lrecl=500;

 

                keep Date Machine Location;

           run;

 

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

                data abs_player_billsin;

                     set _abs_player_billsin (obs=0);

                run;

           %end;

 

           proc append

                data = _abs_player_billsin

                base = abs_player_billsin;

           run;

 

           proc datasets lib=work nolist nowarn;

                delete     _abs_player_billsin;

           quit;

 

     %end;

%end;

%mend extract_playerbillin;

%extract_playerbillin;

 

Esteemed Advisor
Posts: 6,634

Re: Using DO LOOP to import excel files

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
Esteemed Advisor
Esteemed Advisor
Posts: 7,189

Re: Using DO LOOP to import excel files

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
  • 113 views
  • 0 likes
  • 3 in conversation