sas query

[rakeshvvv]

Hi everyone, Good morning

I have a dataset with four variables ID, VISIT , ORD and DATE

100020001    1    2    2014-10-23

100020001    1.02    2    2014-10-24T11:00

100020001    2    2    2014-11-13

100020001    3    2    2014-12-04

100020001    4    2    2014-12-29

100020001    5    2    2015-01-19

100020001    6    2    2015-02-09

100020001    7    3    2015-04-08

100020001    8    3    2015-05-29

100020001    9    3    2015-06-29

100020001    10    3    2015-07-29

I should write a query in such a way that i should output only two records per ID.

1ST RECORD Will be the one where visit=1

2nd record should first visit associated when ord=3

the output should be like this....

100020001    1    2    2014-10-23

100020001    7    3    2015-04-08

Can someone help on this...

Thanks

[RW9]

Hi,

Assuming your data is sorted correctly:

data want;

     set have;

     by id visit ord;

     if first.ord and ord in (1,3) then output;

run;

[rakeshvvv]

Hi,

It has been sorted correct only....ORD can have only two values 2 and 3.....was it typo error or does ord in (1,3) has any significance.....

[RW9]

Thats why it need to be sorted.  This line:

     if first.ord and ord in (1,3) then output;

Firstly it will only trigger on the first occurence of ord within the group id, visit.  The and ord in (1,3) is there to only output if that ord variable is 1 or 3, if you remove that part, you ill get the first occurence of 2 as well, which by your text you only wanted 1,3.  Actually re-reading the post it should just be the:

   if first.ord then output;

AS you only have 2 and 3, which is what you want.  So update is:

data want;

     set have;

     by id visit ord;

     if first.ord then output;

run;

[rakeshvvv]

Hi

the below program from BRUNO SILVA is giving the desired result....can you suggest any other method apart from using RETAIN....

1. data have; 
2. infile cards truncover expandtabs; 
3. input id visit ord (date) (yymmdd10.); 
4. format date yymmdd10.; 
5. cards; 
6. 100020001 1 2 2014-10-23 
7. 100020001 1.02 2 2014-10-24T11:00 
8. 100020001 2 2 2014-11-13 
9. 100020001 3 2 2014-12-04 
10. 100020001 4 2 2014-12-29 
11. 100020001 5 2 2015-01-19 
12. 100020001 6 2 2015-02-09 
13. 100020001 7 3 2015-04-08 
14. 100020001 8 3 2015-05-29 
15. 100020001 9 3 2015-06-29 
16. 100020001 10 3 2015-07-29 
17. ; 
18. run; 
19. data want; 
20.   set have; 
21.   retain rul1 0 rul2 0; 
22.   if rul1 eq 0 and visit eq 1 then do; 
23.   rul1=1; 
24.   output; 
25.   end; 
26.   if rul2 eq 0 and ord eq 3 then do; 
27.   rul2=1; 
28.   output; 
29.   end; 
30.   drop rul1 rul2; 
31. run;

[BrunoSilva · Accepted solution]

Hello,

How about this:

data have;
infile cards truncover expandtabs;
input id visit ord (date) (yymmdd10.);
format date yymmdd10.;
cards;
100020001 1 2 2014-10-23
100020001 1.02 2 2014-10-24T11:00
100020001 2 2 2014-11-13
100020001 3 2 2014-12-04
100020001 4 2 2014-12-29
100020001 5 2 2015-01-19
100020001 6 2 2015-02-09
100020001 7 3 2015-04-08
100020001 8 3 2015-05-29
100020001 9 3 2015-06-29
100020001 10 3 2015-07-29
;
run;


data want;
  set have;
  retain rul1 0 rul2 0;
  if rul1 eq 0 and visit eq 1 then do;
  rul1=1;
  output;
  end;


  if rul2 eq 0 and ord eq 3 then do;
  rul2=1;
  output;
  end;
  drop rul1 rul2;
run;