topic Re: pick the closet value aginst time period in SAS Procedures

pick the closet value aginst time period

sam369 — Fri, 10 Apr 2015 15:44:02 GMT

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;

1.5

1.8

4.87

6.8

7.9

8.7

9.8

11.5

11.9

;

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

Re: pick the closet value aginst time period

art297 — Fri, 10 Apr 2015 16:25:30 GMT

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;

Re: pick the closet value aginst time period

sam369 — Fri, 10 Apr 2015 17:12:31 GMT

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

Re: pick the closet value aginst time period

art297 — Fri, 10 Apr 2015 17:34:57 GMT

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;

Re: pick the closet value aginst time period

slchen — Fri, 10 Apr 2015 19:23:36 GMT

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;

stop;

keep i new_value;

run;

Re: pick the closet value aginst time period

stat_sas — Sat, 11 Apr 2015 00:48:25 GMT

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;

Re: pick the closet value aginst time period

Ksharp — Sat, 11 Apr 2015 09:37:55 GMT

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

Re: pick the closet value aginst time period

sam369 — Mon, 13 Apr 2015 01:18:22 GMT

Thank you all, every method work like a champ..

Thanks

Sam

Re: pick the closet value aginst time period

CharlotteCain — Mon, 13 Apr 2015 07:31:05 GMT

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

Re: pick the closet value aginst time period

Ksharp — Mon, 13 Apr 2015 11:46:00 GMT

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