Basic looping question using macro variable and observation

[ArunprasadT]

Hello,

I have a basic looping question inside a dataset to call the import macro based on the total number of import file list given in the source data set.

data outdir.imp_lkup_files;
 length dsn $40 xlsheet $20 filenm $150;
 infile datalines DSD Delimiter='|';
 input dsn $ xlsheet $ filenm $;
datalines;
outdir.Feb|planf|K:\user\plan_f_20160204
outdir.Mar|planm|K:\user\plan_m_20160322
outdir.Apr|plana|K:\user\plan_a_20160407
;
run;

This source dataset contains 3 observations which i have stored in a macro variable ilkup_cnt = 3.

Then, i have a macro to import the files into a dataset dynamically.

%macro import(out_dsn=,file=,sheet=);
PROC IMPORT OUT= &out_dsn. DATAFILE= &file.
            DBMS=xlsx REPLACE;
     SHEET=&sheet.;
     GETNAMES=YES;
RUN;
%mend import;

I am calling this macro in another main macro called file_imp_exp, like below:

%macro file_imp_exp(action=);
%if %upcase(&action)=IMPORT %then %do;
 %if &ilkup_cnt. ge 1 %then %do;
  data _null_;
   set outdir.imp_lkup_files;
   %do i = 1 %to &ilkup_cnt.;
    call symput('imp_dsn',dsn);
    call symput('imp_sheet',xlsheet);
    call symput('imp_file',filenm);
    %import(out_dsn=&imp_dsn.,file=&imp_file.,sheet=&imp_sheet.);
   %end;
  run;
 %end;
%end;
%else %if %upcase(&action)=EXPORT %then %do;
.
.
.
%end;
%mend file_imp_exp;

%file_imp_exp(action=import);

I can see only the 3rd file is imported into a dataset but not the first and second. Can you please suggest how to use the looping between the observations?

[Astounding]

Macro statements are not part of a DATA step.  %DO, and %IMPORT cannot be executed within a DATA step.  The most they can do is construct SAS language statements that become part of the DATA step.

To fix the problem, here is one approach that changes the looping.  Within the macro:

 %if &ilkup_cnt. ge 1 %then %do i=1 %to &ilkup_cnt.;
  data _null_;
   set outdir.imp_lkup_files (firstobs=&i obs=&i);
    call symput('imp_dsn',dsn);
    call symput('imp_sheet',xlsheet);
    call symput('imp_file',filenm);

  run;
    %import(out_dsn=&imp_dsn.,file=&imp_file.,sheet=&imp_sheet.)
 %end;

[Reeza]

You may want to look into call execute, especially if you have your parameters in a data set. 

[RW9]

Hi,

As @Reeza has stated, add one procedure to your datastep and avoid all that messy code:

data outdir.imp_lkup_files;
  length dsn $40 xlsheet $20 filenm $150;
  infile datalines DSD Delimiter='|';
  input dsn $ xlsheet $ filenm $;
/* Here */
  call execute(cat('proc import datafile=',strip(filenm),' out=',strip(dsn),' dbms=xlsx replace; sheet="',strip(xlsheet),'"; getnames=yes; run;'));
datalines;
outdir.Feb|planf|K:\user\plan_f_20160204
outdir.Mar|planm|K:\user\plan_m_20160322
outdir.Apr|plana|K:\user\plan_a_20160407
;
run;