topic Re: Converting SQL Code to SAS in SAS Programming

Converting SQL Code to SAS

Data_User — Thu, 13 Jul 2017 10:05:27 GMT

Hi,

Is there a way to convert the below code to work in SAS? Specifically I am looking for alternative options for "decode", "sign", "to_number" & "to_char" functions used in the below code. Any help on this is highly appreciated.

proc sql;

create table test1 as

select a.var1,

count(c.var2) as var2 format=comma20.2,

sum(c.var3) as var3 format=comma20.2,

sum(a.var4) as var4 format=comma20.2,

decode( c.var5,14,a.var6,

decode( sign(to_number( to_char( d.var7,'yyyymmdd') )),2,500,

a.var6))

from db.table1 as a, db.table2 as b, db.table3 as c, db.table4 as d

where a.key1 = d.key1

and b.key2 = a.key2

and a.key3 = c.key3

and b.date is null

group by a.var1,

decode( c.var5,14,a.var6,

decode( sign(to_number( to_char( d.var7,'yyyymmdd') )),2,500,

a.var6))

order by 1 asc;

quit;

Thanks

Re: Converting SQL Code to SAS

Oligolas — Thu, 13 Jul 2017 10:21:20 GMT

Hi,

yes, you can use the put and input functions to decode a format

select...

put(a.sex,$Sex.) as Gender

....

from

...

Re: Converting SQL Code to SAS

RW9 — Thu, 13 Jul 2017 10:41:16 GMT

Yes:

decode - you can replace this in a couple of ways, formats, or case when <> then <> else <> end statements.

to_number - you can replace this with input(<>,best.)

to_char - you can replace with put(<>,best

sign - again number of different ways but case when <> < 0 then -1 when <> =0 then 0 else 1 end

(note <> is your logic or variable).

Also note, I dont think that SQL code will wok as you expect as you are aggregating (sum,count) by a group, but you also have where clauses and variables which are not aggregated.