CODEWITHSUNDEEP
sqlvariablesif-statementsassas-macro
Ela
Ela

Reputation: 11

SAS SQL create table depending on macro variable result

I am struggling with creating a new table in proc sql SAS depending on macro variable result.

1) I want to check if necessary table exists.

2) If it exists then I want to create a new table with given parameters.

3) If it doesn't exist I want to create a new table with different parameters.

I think I know how to check if table exists (0 or 1 in log results):

        %let tex1 = %sysfunc(exist(Base.pk_&monthP1));   
        %put tex1 = &tex1.;

But I do not know how to implement this result into proc sql statement.

I need sth like this:

        proc sql;
        create table test as
        select case when &text1 = 0 then select ...
        else 
        select ...
        end ;
        quit;

Thank you in advance for suggested solutions.

Upvotes: 0

Views: 1505

Answers (1)

Tom
Tom

Reputation: 51566

So if both tables have the same structure then the only part of the SQL code that needs to change is the FROM clause. It is probably easier to conditionally set a macro variable to the name to use and replace the name of the dataset with a reference to the macro variable.

select var1,varb, ....
  from &dsname.

Now the problem becomes one of just setting the macro variable. You could do that with macro logic. But you could also just do that with data step logic.

data _null_ ;
  if exist("Base.pk_&monthP1") then call symputx('dsname','table1');
  else call symputx('dsname','table2');
run;
proc sql;
   ... from &dsname. ...

Upvotes: 0

Related Questions