Hi All,
I have the following data set. i need to pick the values close to 0,3,6,9,12.....(time period)
data have;
input value;
cards;
0
1.5
1.8
4
4.87
6.8
7.9
8.7
9.8
11
11.5
11.9
14
;
run;
want:
value
0 (picking this value is <=0)
1.8 (picking this value is <3)
4.87 (picking this value is <6)
8.7 (picking this value is <9)
11.9 (picking this value is <12)
Thanks
Sam
Same question as other's .
data have; input value; cards; 0 1.5 1.8 4 4.87 6.8 7.9 8.7 9.8 11 11.5 11.9 14 ; run; %let weeks=4; data key; do i=0 to &weeks; value=3*i;output; end; drop i; run; data want; set have key(in=inb); by value; v=lag(value); if inb and not lag(inb) ; keep v; run;
Xia Keshan
You could do it fairly easily using proc sql. e.g.,
proc sql;
select max(in_value) as value,
case
when in_value le 0 then '(picking this value is <= 0)'
when in_value lt 3 then '(picking this value is < 3)'
when in_value lt 6 then '(picking this value is < 6)'
when in_value lt 9 then '(picking this value is < 9)'
when in_value lt 12 then '(picking this value is < 12)'
else 'outside of range'
end as vgroup
from have (rename=(value=in_value))
where in_value le 12
group by calculated vgroup
order by in_value
;
quit;
Thank you Art!!!! But in above example, i gave a example for time period up to 12, but in my real data time period is up to 300.
i am something looking in loop.
do i =0 to 300 by 3 :- time period
Thanks
Sam
You can always use the same approach but submit it using call execute. e.g.:
data _null_;
length excmd $200;
call execute('proc sql;');
call execute('select max(in_value) as value,');
call execute(' case');
excmd=catt("when in_value le 0 then '",
"(picking this value is <= 0)'");
call execute(excmd);
do i=3 to 300 by 3;
excmd=catx(' ','when in_value lt',i,
"then '(picking this value is <",i,")'");
call execute(excmd);
end;
call execute("else 'outside of range'");
call execute('end as vgroup');
call execute(' from have (rename=(value=in_value))');
call execute(' where in_value le 300');
call execute(' group by calculated vgroup');
call execute(' order by in_value');
call execute(';');
call execute('quit;');
run;
data want;
length new_value $50.;
do i=0 to 12 by 3;
do p=1 to nobs;
set have point=p nobs=nobs;
num=i-value;
_value=lag(value);
if num<0 then do;
new_value=catt(_value,' (picking this value is <', _value,')');
output;
leave;
end;
end;
end;
stop;
keep i new_value;
run;
data want;
set have;
do i=0 to 300 by 3;
distance=abs(value-i);
output;
end;
run;
proc sql;
select value from want
where value<=i
group by i
having distance=min(distance);
quit;
Same question as other's .
data have; input value; cards; 0 1.5 1.8 4 4.87 6.8 7.9 8.7 9.8 11 11.5 11.9 14 ; run; %let weeks=4; data key; do i=0 to &weeks; value=3*i;output; end; drop i; run; data want; set have key(in=inb); by value; v=lag(value); if inb and not lag(inb) ; keep v; run;
Xia Keshan
Hi Xia, Good morning from England and I guess Good afternoon in China. I hope you are well. Sorry for the bother, I liked the way you applied the interleaving concept of multiple datasets in set statement followed by a BY statement. Pretty cool. But in your code, i wondering where you need
data want;
set have key(in=inb);
by value;
v=lag(value);
if inb and not lag(inb) ;/* is not lag(inb) required?, because it seems to work fine with just if inb;*/
keep v;
run;
I am sure Xia Keshan is never wrong, so it has to be with my understanding, therefore can you please explain that part? Thanks for your time.
Charlotte
Hi Charlotte ,
I am starting to busy now. Only have some time at evening to review and answer these post.
"I am sure Xia Keshan is never wrong"
Thanks your valuable compliment ! I appreciated .
"/* is not lag(inb) required?, because it seems to work fine with just if inb;*/"
I think that is required . if there are not obs between 3 and 6 like following , You will get wrong result.
data have; input value; cards; 0 1.5 6.8 7.9 8.7 9.8 11 11.5 11.9 14 ; run; %let weeks=4; data key; do i=0 to &weeks; value=3*i;output; end; drop i; run; data want; set have key(in=inb); by value; v=lag(value); if inb ; keep v; run;
Best
Xia Keshan
Thank you all, every method work like a champ..
Thanks
Sam
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.