topic Re: Help in my sql code in SAS Programming

Help in my sql code

knveraraju91 — Sun, 21 Jun 2020 17:22:14 GMT

Dear,

I need help in my below sql code. Please suggest. I need to calculate max decimals in value for var. Some one helped me yesterday. Thank you

I am having difficulty where obs having missing values. For obs e=. and f=. For these obs, prec value should be missing

output needed

var value prec

a 100.01 3
a 110.003 3
a 100 3
b 1.1 1
b 2 1
c 5.000002 6
c 6.01 6
c 4 6
d 2 0
d 5 0
d 1120 0
e .
e 100 2
e 10.02 2
f .
f 10 0

data have;
input var $ value;
datalines;
a 100.01
a 110.003
a 100
b 1.1
b 2
c 5.000002
c 6.01
c 4
d 2
d 5
d 1120
e .
e 100
e 10.02
f .
f 10
;

proc sql;
create table want as
select *,
    (select max(lengthn(scan(put(value, best32.), 2, "."))) from have where not missing(value) group by var) as prec 
from have
;

quit;

Re: Help in my sql code

PGStats — Sun, 21 Jun 2020 18:29:38 GMT

proc sql;
create table want as
select 
    *,
    case when value is missing then . 
        else max(lengthn(scan(put(value, best32.), 2, "."))) end as prec
from have
group by var;
quit;

Re: Help in my sql code

Jagadishkatam — Sun, 21 Jun 2020 18:47:09 GMT

please try below code


proc sql;
create table want as select a.*, case when value ne . and b.dec ne . then b.dec when value ne . and b.dec eq . then 0 else .  end as prec from have as a left join 
(select distinct var, case when value ne . and scan(put(value,best.),2,'.') ne '' then max(length(scan(put(value,best.),2,'.'))) else . end as  dec  from have group by var having calculated dec ne .) as b on a.var=b.var;
quit;