pick the closet value aginst time period

[sam369]

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

[art297]

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;

[sam369]

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

[art297]

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;

[slchen]

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;

[stat_sas]

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;

[Ksharp · Accepted solution]

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

[CharlotteCain]

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

[Ksharp]

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

[sam369]

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

Thanks

Sam