DATA Step, Macro, Functions and more

Converting SQL Code to SAS

Reply
Contributor
Posts: 43

Converting SQL Code to SAS

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

Frequent Contributor
Posts: 129

Re: Converting SQL Code to SAS

Posted in reply to Data_User

Hi,

 

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

 

select...

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

....

from

...

 

________________________

- Cheers -

Super User
Super User
Posts: 7,997

Re: Converting SQL Code to SAS

Posted in reply to Data_User

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.  

Ask a Question
Discussion stats
  • 2 replies
  • 89 views
  • 0 likes
  • 3 in conversation