Help using Base SAS procedures

sas query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

sas query

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


Accepted Solutions
Solution
‎07-22-2015 09:42 AM
Contributor
Posts: 50

Re: sas query

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: sas query

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;

Frequent Contributor
Posts: 145

Re: sas query

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.....

Super User
Super User
Posts: 7,392

Re: sas query

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;

Frequent Contributor
Posts: 145

Re: sas query

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. run; 
  18. data want; 
  19.   set have; 
  20.   retain rul1 0 rul2 0; 
  21.   if rul1 eq 0 and visit eq 1 then do; 
  22.   rul1=1; 
  23.   output; 
  24.   end; 
  25.   if rul2 eq 0 and ord eq 3 then do; 
  26.   rul2=1; 
  27.   output; 
  28.   end; 
  29.   drop rul1 rul2; 
  30. run;
Solution
‎07-22-2015 09:42 AM
Contributor
Posts: 50

Re: sas query

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 357 views
  • 3 likes
  • 3 in conversation