topic Re: sas query in SAS Procedures

sas query

rakeshvvv — Wed, 22 Jul 2015 13:16:12 GMT

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

Re: sas query

RW9 — Wed, 22 Jul 2015 13:41:05 GMT

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;

Re: sas query

BrunoSilva — Wed, 22 Jul 2015 13:42:58 GMT

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;

Re: sas query

rakeshvvv — Wed, 22 Jul 2015 13:46:01 GMT

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

Re: sas query

RW9 — Wed, 22 Jul 2015 13:53:40 GMT

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;

Re: sas query

rakeshvvv — Wed, 22 Jul 2015 14:39:48 GMT

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

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;