BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;
2 REPLIES 2
PGStats
Opal | Level 21
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;
PG
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1018 views
  • 3 likes
  • 3 in conversation