## pick the closet value aginst time period

Solved
Regular Contributor
Posts: 171

# pick the closet value aginst time period

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

Accepted Solutions
Solution
‎04-11-2015 05:37 AM
Super User
Posts: 10,782

## Re: pick the closet value aginst time period

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

All Replies
PROC Star
Posts: 8,164

## Re: pick the closet value aginst time period

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;

Regular Contributor
Posts: 171

## Re: pick the closet value aginst time period

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

PROC Star
Posts: 8,164

## Re: pick the closet value aginst time period

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;

Super Contributor
Posts: 275

## Re: pick the closet value aginst time period

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;

Posts: 1,270

## Re: pick the closet value aginst time period

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;

Solution
‎04-11-2015 05:37 AM
Super User
Posts: 10,782

## Re: pick the closet value aginst time period

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

Frequent Contributor
Posts: 140

## Re: pick the closet value aginst time period

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

Super User
Posts: 10,782

## Re: pick the closet value aginst time period

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

Regular Contributor
Posts: 171

## Re: pick the closet value aginst time period

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

Thanks

Sam

🔒 This topic is solved and locked.