Help using Base SAS procedures

Writing conditions in Proc Sql based on user defined variables

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Writing conditions in Proc Sql based on user defined variables

Hi all,

suppose I've got a code like this:

proc sql;

     create table DATASET1 as select

     case when a.VAR1 + a.VAR2 > 3 then 'UVAR1'

          end as MYVAR1,

     case when a.VAR1 + a.VAR3 = 0 then 'UVAR2'

          end as MYVAR2

     from DATASET;

quit;

I would like to add an extra 'case when', but instead of using variables from DATASET I would like to use my variables MYVAR1 and MYVAR2. Is there a way to do that?

thanks


Accepted Solutions
Solution
‎04-01-2014 07:43 AM
Super User
Posts: 5,260

Re: Writing conditions in Proc Sql based on user defined variables

Actually, there is.

Use the keyword Calculated, which an extremely useful SAS extension to ANSI SQL. It's more cumbersome to solve this in other SQL dialects.

Data never sleeps

View solution in original post


All Replies
Solution
‎04-01-2014 07:43 AM
Super User
Posts: 5,260

Re: Writing conditions in Proc Sql based on user defined variables

Actually, there is.

Use the keyword Calculated, which an extremely useful SAS extension to ANSI SQL. It's more cumbersome to solve this in other SQL dialects.

Data never sleeps
Super User
Super User
Posts: 7,413

Re: Writing conditions in Proc Sql based on user defined variables

Hi,

As LinusH mentioned there is calculated.  However you can also do these things by subquerying.  There are times when each method is useful, depending on the complexity of the SQL being created.  The below is logically the same, the first using a value which is calculated within the same select, the second having that value created as part of the select in the subquery.  In this simple example it may look a bit pointless, however if you are writing large queries which utilize many datasets, calculations, grouping etc. (say something like and ADSL table) then it can be useful to pre-process data going into your query to simplify things.

proc sql;
  create table ... as
  select  values,
          case when else,
          case calculated when else
  from    a_dataset;
quit;

proc sql;
  create table ... as
  select  values,
             case when calculated else
  from    (select values,
                  case when else as calculated
           from   a_dateset);
quit;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 187 views
  • 0 likes
  • 3 in conversation