BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Ammonite | Level 13

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
Super User

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
Quartz | Level 8

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
Super User

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
Obsidian | Level 7

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

Thanks

Sam

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1164 views
  • 8 likes
  • 6 in conversation