CODEWITHSUNDEEP
sqlsasoracle-sqldeveloper
PinkyL
PinkyL

Reputation: 351

how to write sql query from sas based on if then statements

I don't really have any advanced sql knowledge on how to phrase if/then statements in sql (whether to use case, for example) and/or formatting variables in the same query so I was wondering if someone could help with this sas code and phrasing it in the correct format into sql:

data convert_code;

format date1 date 2 mmddyy8. code $4.;

set userid.code; (this table is pulled from oracle)

if ID='X' then P='A'; else P='B';

If CAT in ('1','2','3') then CAT_group='ONE'; else CAT_GROUP='TWO';

if CAT_2 > '0' and CAT_2A >='1' then d=1; else d=0;

date1=datepart(date1);

date2=datepart(date2);

if code='3' and type_code in ('A','B','C') THEN DO;

if P_CODE in ('1','2','3') then P='1';

if P_CODE in ('4','5','6') then P='2';

end;

if code='4' and e_code in ('A') then DO;

if B_CODE in ('11','12','13') then P='3';

if B_CODE in ('14','15','16') then P='4';

end;

run;

Upvotes: 1

Views: 3221

Answers (1)

SRSwift
SRSwift

Reputation: 1710

The example given here uses SAS' proc sql language. If you are using a different SQL implementation then the syntax may be different. However, the case expression examples should be fairly easy to adapt to any SQL implementation as it is part of the SQL standard.

proc sql;
    /* Name your output table */
    create table convert_code as
    select
        /* 
            Unlike the data step variables from the input data are not
            selected by default, you can request them individually or
            with "select *" 
        */
        /* Use "format =" after the column definition to set its format */
        code format = $4.,
        /* Use SAS functions as normal, name the output variable with "as" */
        datepart(date1) as date1 format = mmddyy8.,
        /* Comma separate each variable you want in your output */
        datepart(date2) as date2 format = mmddyy8.,
        /* A "case" expression can conditionally set a variable to a value */
        case 
            when CAT in ('1', '2', '3') then 'ONE'
            else 'TWO'
        /* Close the "case" statement with "end" */
        end as CAT_group,
        /* You can nest case statements to emulate your "then do" */
        case 
            when code = '3' and e_code in ('A', 'B', 'C') then 
                case 
                    /* Use multiple "when then"s to emulate "else if" */
                    when P_CODE in ('1', '2', '3') then '1'
                    when P_CODE in ('4', '5', '6') then '2'
                    else ''
                end
            when code = '4' and e_code in ('A') then 
                case 
                    when P_CODE in ('11', '12', '13') then '3'
                    when P_CODE in ('14', '15', '16') then '4'
                    else ''
                end
            when ID = 'X' then 'A'
            else 'B'
        end as P,
        /* An alternative to case is to use the "ifn()" or "ifc()" functions */
        ifn(CAT_2 > 0 and CAT_2A >= 1, 1, 2) as d
    /* Choose the input data and end the query with a ";" */
    from userid.code;
    /* 
        Additional processing can be done here, some examples include:
            "where": limit the input
            "left join", "right join", "inner join", "outer join", ",":
                combine with additional data sets
            "group by": group based on column values for summary functions
            "order by": specify which columns to sort the output by
    */
/* End the "proc sql" processing */
quit;

Using case expressions rather than the the SAS specific ifn() and ifc() functions would be my suggestion, as they are the standard SQL method for conditionally assigning values and will (probably) be the same in other SQL implementations. Be aware that format = and the datepart() function are SAS specific.

  • A brief exploration of proc sql from a data step perspective can be found here
  • The case expression here
  • The ifn() and ifc() functions here
  • The format = column modifier here

Upvotes: 1

Related Questions