topic Re: If then set statement in SAS Programming

If then set statement

polpel — Mon, 14 Oct 2019 09:11:42 GMT

Hello,

I was wondering if there was a way of conditioning a set statement. What I mean is adding an if to this set. I know it would be possible to do so outside of the set, but I'm having to set multiple tables at once...

Here's my code:

data last;

set table1 table2 table3;

where var=&value.;

run;

what I'd like is something like this (if it exists...):

data last;

set

if var = value then; table1

if var = value then; table2

if var = value then; table3

where var2=&value2.;

run;

Re: If then set statement

andreas_lds — Mon, 14 Oct 2019 09:31:21 GMT

Please explain what you trying to achieve by posting some example observations of all used datasets. Also not that the declarative part of the set statement can't be excluded from execution at all. That's why

if 0 then set wanted_vars;

can be used to clone the structure of one dataset, but prevent the observations from being read.

Re: If then set statement

Amir — Mon, 14 Oct 2019 09:38:19 GMT

Note that the if conditions you have specified are all the same so it will be true for all or false for all.

Further the if conditions appear to be relying on program (not macro) variables, but as no data sets have been read in and no variables have been initialised, the variables in the if conditions would have missing values.

Is this what you intended?

Amir.

Re: If then set statement

polpel — Mon, 14 Oct 2019 09:43:40 GMT

I didn't explain it very well.

What I'd like is that if the table exists, then I add it to the set statement:

set

if (table1 exists) then table1

if (table2 exists) then table2

if (table3 exists) then table3

Re: If then set statement

Amir — Mon, 14 Oct 2019 09:51:12 GMT

You have the right function name - exist(), as per the documentation:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000210903.htm

HTH.

Amir.

Re: If then set statement

polpel — Mon, 14 Oct 2019 10:07:44 GMT

wow thanks a lot!

is it possible to include this in a set statement? if not how might i procede to set the table only if it exists knowing that i'm adding multiple tables to the set statement. would i have to use a PROC statement?

Re: If then set statement

Shmuel — Mon, 14 Oct 2019 10:28:31 GMT

There are more than one method to the EXIST function:

The short one is to use it within the macro function %SYSFUNC:

data want;
   set
       %if %sysfunc(exist(table1)) %then %str(<table1_name>)
       %if %sysfunc(exist(table2)) %then %str(<table2_name>)
      ...
; run;

Alternatively assign a macro variable with existing names and use it:

data _NULL_;
       length tables $30; /*adapt to max length need */
        if exist("<table1_name>") then tables = strip("<table1_name>");
        if exist("<table2_name>") then tables = 
                   catx(' ',tables,strip("<table2_name>"));
        if exist("<table3_name>") then tables = 
                   catx(' ',tables,strip("<table3_name>"));
       ....
       call symput('tables', strip(tables));
run;

data want;
   set &tables;
run;

Re: If then set statement

Amir — Mon, 14 Oct 2019 10:29:22 GMT

In this case I would recommend creating a macro. For example the following code creates data sets have1 and have3 (not have2) and only uses the data sets that exist:

/* create have1 & have3, not have2 */
data have1 have3;
   set sashelp.class;
run;

/* define a macro to return the given data set name if it exists */
%macro use_ds_if_exists(ds);
   %if %sysfunc(exist(&ds)) %then
      &ds;
%mend use_ds_if_exists;

/* see what the macro generates when called */
options mprint;

/* invoke the macro in the data step */
data want;
   set
      %use_ds_if_exists(have1)
      %use_ds_if_exists(have2)
      %use_ds_if_exists(have3)
   ;
run;

NB. Note that if none of the data sets exist then you have to consider what happens.

Please remember to mark any solution posted you think works.

Amir.

Re: If then set statement

polpel — Mon, 14 Oct 2019 10:35:41 GMT

hello and thanks for the response!

i've tried your first method as it ressembles more to what i was trying at first but i get the following error:

ERROR: Expected %DO not found.
ERROR: Skipping to next %END statement.

regards

DATA combined;
	set
		%if %sysfunc(exist(table1)) %then %str (table1)
		%if %sysfunc(exist(table2)) %then %str (table2)
	;
	where year = &year.;
RUN;

Re: If then set statement

FreelanceReinh — Mon, 14 Oct 2019 10:36:50 GMT

Or use CALL EXECUTE:

data _null_;
length dsn $32;
call execute('data last; set');
do dsn='table1', 'table2', 'table3';
  call execute(ifc(exist(dsn),dsn,'_null_'));
end;
call execute('; where var2=&value2; run;');
run;

Re: If then set statement

Patrick — Mon, 14 Oct 2019 10:50:23 GMT

@polpel wrote:

wow thanks a lot!

is it possible to include this in a set statement? if not how might i procede to set the table only if it exists knowing that i'm adding multiple tables to the set statement. would i have to use a PROC statement?

You could do something like here: http://support.sas.com/kb/24/670.html

Code as below should also work:

data want;
  set
    %sysfunc(ifc(%sysfunc(exist(sashelp.class)),sashelp.class,))
    %sysfunc(ifc(%sysfunc(exist(sashelp.somethingelse)),sashelp.somethingelse,))
    indsname=_dsin;
    ;
    dsin=_dsin;
run;

Re: If then set statement

Quentin — Mon, 14 Oct 2019 13:13:27 GMT

@polpel wrote:

hello and thanks for the response!

i've tried your first method as it ressembles more to what i was trying at first but i get the following error:

ERROR: Expected %DO not found.
ERROR: Skipping to next %END statement.

regards
DATA combined;
	set
		%if %sysfunc(exist(table1)) %then %str (table1)
		%if %sysfunc(exist(table2)) %then %str (table2)
	;
	where year = &year.;
RUN;

You need a semicolon for each %IF statement:

DATA combined;
	set
		%if %sysfunc(exist(table1)) %then table1 ; /*semicolon ends the %IF statement*/
		%if %sysfunc(exist(table2)) %then table2 ; /*semicolon ends the %IF statement*/
	; /*semicolon ends the SET statement*/
	where year = &year.;
RUN;

It looks funny until your eyes get used to it, but the semicolons end the macro language %IF statements, they do not end the SET statement. They are macro language semicolons, not DATA step language semicolons.