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
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;
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;
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.....
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;
Hi
the below program from BRUNO SILVA is giving the desired result....can you suggest any other method apart from using RETAIN....
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.