topic Re: Basic looping question using macro variable and observation in SAS Programming

Basic looping question using macro variable and observation

ArunprasadT — Thu, 23 Jun 2016 19:02:15 GMT

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.

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?

Re: Basic looping question using macro variable and observation

Astounding — Thu, 23 Jun 2016 19:27:45 GMT

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;

Re: Basic looping question using macro variable and observation

Reeza — Thu, 23 Jun 2016 19:50:41 GMT

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

Re: Basic looping question using macro variable and observation

RW9 — Fri, 24 Jun 2016 08:22:31 GMT

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;