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
Hi,
yes, you can use the put and input functions to decode a format
select...
put(a.sex,$Sex.) as Gender
....
from
...
- Cheers -
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.