topic Macro Question in New SAS User

Macro Question

SASMom2 — Thu, 06 Feb 2020 04:54:11 GMT

I have below macro in my SAS code. I have total of 2,000 customers that I run a report for. I have assigned them values from 1,000 to 3,000. When I run the code, at the end, it creates a table called ‘file_&i_allyrs’. I do not get data for same number of customers every time I run it. Sometimes, I get this table for only 1,000 customers and sometimes for 1,800 customers. The ‘i’ below is customer number assigned between 1000 and 3000.

%macro Read_ALL;

%do i = &beg %to &end;

data cont_&i;

set file_&i_allyrs;

cust = “&i”;

run;

/* appending rest of the file_&i._allyrs tables */

proc append base = all_files_allyears

data = cont_&i force;

run;

%end;

%mend ;

%macro byyr(beg, end);

%Read_ALL;

%mend;

%byyr(1000, 3000);

So, first it reads 'file_1000_allyrs' table. If this file does not exist, it gives an error and goes to read 'file_1001_allyrs' table..and so on.

Every time the file does not exist, it gives an error. Below is part of the error it gives.

ERROR: File FILES_1000_ALLYRS.DATA does not exist.

I would like the macro to skip the file it does not exist OR I would like to stop getting an error if the file does not exist.

How do accomplish that?

Thank you in advance

Re: Macro Question

Patrick — Thu, 06 Feb 2020 23:18:01 GMT

If the numbering of the files would be at the end then you could reference the files simply by using the colon modifier like:

set file_allyrs_:;

Assuming that there could be a table starting with name FILE_... which you don't want included, things become slightly harder.

data file_1000_allyrs file_1555_allyrs file_2987_allyrs file_2987_notallyrs;
  set sashelp.class;
run;

proc sql noprint;
  select catx('.',libname,memname) into :tbl1-:tbl9999
  from dictionary.tables
  where libname='WORK' and memtype='DATA'
    and prxmatch('/^file_\d{4}_allyrs$/oi',strip(memname))>0
  ;
quit;
%macro tables();
  %do i=1 %to &sqlobs;
    &&tbl&i
  %end;
%mend;

data all_files_allyears;
  length _curr_file $32. cust $4;
  retain cust;
  set %tables() indsname=_curr_file;
  if lag(_curr_file) ne _curr_file then 
    cust = compress(_curr_file,,'kd');
run;

....and I can't help myself to also write: You probably should revisit your upstream processing so that you don't end up with multiple tables in first place. Given the power of SAS By-Group processing there is almost never a good reason to split up tables the way it's done here.

Using your code here how this could work:


%macro Read_ALL;
  %do i = &beg %to &end;
    %if %sysfunc(exist(file_&i._allyrs,data)) %then
      %do;
        data cont_&i;
          set file_&i._allyrs;
          cust = "&i";
        run;

        /* appending rest of the file_&i._allyrs tables */
        proc append base = all_files_allyears
          data = cont_&i force;
        run;
      %end;
  %end;
%mend;

%macro byyr(beg, end);
  %Read_ALL;
%mend;

%byyr(1000, 3000);

Re: Macro Question

Ksharp — Thu, 06 Feb 2020 06:03:54 GMT

Add function EXIST() to judge if it existed.

%macro Read_ALL;

%do i = &beg %to &end;

%if %sysfunc(exist(file_&i_allyrs)) %then %do;
data cont_&i;
set file_&i_allyrs;
cust = “&i”;
run;
proc append base = all_files_allyears
           data = cont_&i force;
run;
%end;

       %end;

%mend ;

Re: Macro Question

SASMom2 — Thu, 06 Feb 2020 21:18:43 GMT

Thank you for your quick reply. That worked!