If then set statement

[polpel]

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;

[andreas_lds]

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.

[Amir]

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.

[polpel]

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

[Amir]

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.

[polpel]

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?

[Shmuel]

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;

[polpel]

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;

[Quentin]

---

@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.

[Amir · Accepted solution]

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.

[FreelanceReinh]

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;

[Patrick]

---

@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;