DATA Step, Macro, Functions and more

Ignore missing table when setting multiple tables in data set

Reply
Contributor
Posts: 41

Ignore missing table when setting multiple tables in data set

I have a script that 95% of the time works perfectly but every now and then there is an error that causes the entire program to stop. 

 

My code runs 42 regressions and the results from these regressions are placed in 42 individual tables and then those 42 tables are appended to one another to make a master table. Some times there is no data for 1 or more of the 42 regressions so no table is produced. If one or more of the 42 tables does not exist, the program hits an error when the 42 tables are appended together.

 

data work.appended;
set
work.monday_em_t work.monday_da_t work.monday_ef_t work.monday_pr_t work.monday_lf_t work.monday_ov_t
work.tuesday_em_t work.tuesday_da_t work.tuesday_ef_t work.tuesday_pr_t work.tuesday_lf_t work.tuesday_ov_t
work.wednesday_em_t work.wednesday_da_t work.wednesday_ef_t work.wednesday_pr_t work.wednesday_lf_t work.wednesday_ov_t
work.thursday_em_t work.thursday_da_t work.thursday_ef_t work.thursday_pr_t work.thursday_lf_t work.thursday_ov_t
work.friday_em_t work.friday_da_t work.friday_ef_t work.friday_pr_t work.friday_lf_t work.friday_ov_t
work.saturday_em_t work.saturday_da_t work.saturday_ef_t work.saturday_pr_t work.saturday_lf_t work.saturday_ov_t
work.sunday_em_t work.sunday_da_t work.sunday_ef_t work.sunday_pr_t work.sunday_lf_t work.sunday_ov_t;
run;

 

For instance, if there is no "work.saturday_em_t" table, the code above will create an error and not produce an appended file. 

 

I would like for the code to just ignore if a table is missing and append all tables that do exist without stopping the program. 

 

Is this possible? 

 

 

PROC Star
Posts: 1,312

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

Sounds like 42 individual tables varies and I suppose it could be any number. This is where a macro usage is best. You can save your needed tables for the append in a separate library and store the names in a macrovar list. Call this in a set statement to make it completely dynamic

Contributor
Posts: 41

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to novinosrin

This makes sense. Is it possible to create a temporary library other than the WORK library in SAS? Or would I have to create a physical location for the library? 

 

 

PROC Star
Posts: 1,312

Re: Ignore missing table when setting multiple tables in data set

[ Edited ]
Posted in reply to SmcGarrett

 "Is it possible to create a temporary library other than the WORK library in SAS?"

 

Excellent question. Great minds think alike. You and the super users.  I vaguely remember reading some paper by amadeus UK about that but I can't say for sure. Honestly, I really wish I paid attention to that paper. May be if you hang in there, I am sure some super user will likely give you the answer. 

 

Hmmm, should i just take the liberty to call upon @Reeza requesting to take a look into your needs Smiley Embarassed

Community Manager
Posts: 3,354

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to novinosrin

Yes, you can create a temporary library folder, building it right off of the WORK folder.  Use the DLCREATEDIR option to tell the LIBNAME statement to create a new folder for you.

 

options dlcreatedir;
/* This folder will be deleted at end of session */
libname newfold "%sysfunc(getoption(WORK))/new";

/* put some data sets in that new library */ 
data newfold.a1 newfold.a2 newfold.a3;
 set sashelp.class;
run;

/* create a combined version of these in WORK */
data work.combined;
 set newfold.a:;
run;
PROC Star
Posts: 1,312

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to ChrisHemedinger

@ChrisHemedinger   Thank you Sir, Can't appreciate enough!!!!!!!

Super User
Posts: 22,844

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to novinosrin

I would change my process so that at the end of the regression I was appending the results. There I would take care of the criteria that if a table didn't exist it didn't get appended. You can check the SAS Macro Appendix 9.4 for a set of macros that illustrate how this would work.

 

And if you create a library called USER then all data sets will default there instead of WORK and it functions as a temporary library but not in WORK. 

Super User
Posts: 13,028

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

From the names of all those data sets I would like to know if the data before the regressions is basically same but run on data from different days. If so you might minimize your headaches by 1) combining the input data with a variable to have the day and 2) running the regressions with a BY statement. Then you will have one data set that contains all the days that had data and would not be concerned about any one day missing (though you still have an issue if all of the days were missing).

 

Then your set statement would look like

set work.em_t work.da_t work.ef_t work.pr_t work.lf_t work.ov_t;

 

Though I am not sure what the _t at the end of the sets gains as it is on all of the sets.

 

Super User
Posts: 6,537

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

There are some details that could be important, so here are a few assumptions:

 

  • The names of the data sets are always the same
  • The structure of the data sets are always the same
  • You can examine a working program, and determine the structure of the data sets.

To illustrate, let's say that the data sets contain VARNAME ($ 32) and COEFF (numeric).  I'm sure that's an oversimplification that you will need to embellish.  But  you can code:

 

data work.monday_em_t work.monday_da_t work.monday_ef_t work.monday_pr_t work.monday_lf_t work.monday_ov_t
work.tuesday_em_t work.tuesday_da_t work.tuesday_ef_t work.tuesday_pr_t work.tuesday_lf_t work.tuesday_ov_t
work.wednesday_em_t work.wednesday_da_t work.wednesday_ef_t work.wednesday_pr_t work.wednesday_lf_t work.wednesday_ov_t
work.thursday_em_t work.thursday_da_t work.thursday_ef_t work.thursday_pr_t work.thursday_lf_t work.thursday_ov_t
work.friday_em_t work.friday_da_t work.friday_ef_t work.friday_pr_t work.friday_lf_t work.friday_ov_t
work.saturday_em_t work.saturday_da_t work.saturday_ef_t work.saturday_pr_t work.saturday_lf_t work.saturday_ov_t
work.sunday_em_t work.sunday_da_t work.sunday_ef_t work.sunday_pr_t work.sunday_lf_t work.sunday_ov_t;

length varname $ 32 coeff 8;

call missing (of _all_);

stop;

run;

 

Put that early in your program, and you will have 42 data sets with 0 observations.  The later SET statement won't fail again.

PROC Star
Posts: 2,226

Re: Ignore missing table when setting multiple tables in data set

[ Edited ]
Posted in reply to SmcGarrett

You could prefix your output tables, for example with REGOUT_ to obtain for example REGOUT_MONDAY_EM_T.

 

Then the next data step is just

 

data APPENDED;
  set REGOUT_: ;
run;

 

Super User
Posts: 22,844

Re: Ignore missing table when setting multiple tables in data set

Slight mod on @ChrisNZ solution given your current structure it's not that burdensome to type out the seven days of the week with the : operator to append all the data.

 

data want;
set sunday_:
      monday_:
    tuesday_:
...

saturday_: ;
run;
Contributor
Posts: 35

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

It is worth noting Chris Hemedinger's use of the colon name modifier here.  It may be that instead of specifying all 42 expected datasets, that the following SET statement would suffice:

  SET work.monday_: work.tuesday_: wordk.wednesday_: 

          work.thursday_: work.friday_: work.saturday_: work.sunday_: ;

 

Providing you had at least one dataset for each day of the week, and no others that weren't required in the concatenating SET, this would work.

 

Personally I'd recommend either changing your regressions to use BY processing, or building a macro variable for the dataset list and using the EXIST function to determine whether a name is added to the list.    

PROC Star
Posts: 228

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

One possibility is to create a macro which only lists the datasets that actually exist:

%macro ifexist(datasets);
  %local i w;
  %do i=1 %to %sysfunc(countw(&datasets,%str( )));
    %let w=%scan(&datasets,&i,%str( ));
    %if %sysfunc(exist(&w)) %then %do; &w%end;
    %end;
%mend;
data work.appended;
set
%ifexist(
work.monday_em_t work.monday_da_t work.monday_ef_t work.monday_pr_t work.monday_lf_t work.monday_ov_t
work.tuesday_em_t work.tuesday_da_t work.tuesday_ef_t work.tuesday_pr_t work.tuesday_lf_t work.tuesday_ov_t
work.wednesday_em_t work.wednesday_da_t work.wednesday_ef_t work.wednesday_pr_t work.wednesday_lf_t work.wednesday_ov_t
work.thursday_em_t work.thursday_da_t work.thursday_ef_t work.thursday_pr_t work.thursday_lf_t work.thursday_ov_t
work.friday_em_t work.friday_da_t work.friday_ef_t work.friday_pr_t work.friday_lf_t work.friday_ov_t
work.saturday_em_t work.saturday_da_t work.saturday_ef_t work.saturday_pr_t work.saturday_lf_t work.saturday_ov_t
work.sunday_em_t work.sunday_da_t work.sunday_ef_t work.sunday_pr_t work.sunday_lf_t work.sunday_ov_t
);
run;
Contributor
Posts: 41

Re: Ignore missing table when setting multiple tables in data set

Posted in reply to SmcGarrett

Thanks for the help everyone. I am going to test these out and let you guys know what worked best. 

 

Thanks, 

 

Michael

Ask a Question
Discussion stats
  • 13 replies
  • 267 views
  • 10 likes
  • 9 in conversation