topic Help in my sql code in SAS Programming

Help in my sql code

knveraraju91 — Thu, 25 Jun 2020 18:50:11 GMT

Dear,

I need help in my sql code. Some one helped the code. It worked but i have one more condition that i need to add.

I am getting output i need for all where "var" in a,b,c,d,e,f.

But var=g where i included a "when value lt 1 then 0" Please suggest. Thank you

Output needed for var =g

var value prec

g 0.5 0

g 10 0

output getting

var value prec

g 0.5 0

g 10 1

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
g 0.5
g 10
;
proc sql;
create table want as
select 
    *,
    case when value =. then .
         when value lt 1 then 0 
        else max(lengthn(scan(put(value, best32.), 2, "."))) end as prec
from have
group by var;
quit;

Re: Help in my sql code

yabwon — Thu, 25 Jun 2020 19:25:47 GMT

Hi,

maybe this:

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

Bart

Re: Help in my sql code

mklangley — Thu, 25 Jun 2020 19:29:55 GMT

What exactly are you trying to do with this code? Are you sure your results for a, b, c, d, e, and f are what you expect?

Is prec supposed to be the precision of value? (If so, how do you get prec = 6 for value = 4?)