Hi Ksharp,
I have learned a lot from you. Thank you!
I modified your code slightly.
data x;
input id dt : mmddyy. x high low;
format id z7. dt mmddyy10.;
datalines;
0000001 01/01/2010 50 . 1
0000001 02/01/2010 672 1 .
0000001 03/01/2010 50 . 1
0000002 01/01/2011 50 . 1
0000002 02/01/2011 100 1 .
0000002 03/01/2011 50 . 1
0000002 04/01/2011 50 . 1
0000002 05/01/2011 50 . 1
;
run;
data want(drop=count i);
set x;
count=_n_;
if low=1 and (id=lag(id) and lag(high)=1) and (id=lag2(id) and lag2(low)=1)
then do;
do i=count-2 to count;
set x point=i;
output;
end;
end;
run;
You are welcome.
By the way. Where are you come from?
I found out the Forums about two weeks ago.
I am currently working at a University in Ma. Please let me know if you need a SAS programmer.
Now that I have figured how to keep these three records, I am told I need to transpose the data. I start with this:
Case ID Date X High Low
0000001 01/01/2010 50 . 1
0000001 02/01/2010 672 1 .
0000001 03/01/2010 50 . 1
0000002 01/01/2011 50 . 1
0000002 02/01/2011 100 1 .
0000002 03/01/2011 50 . 1
But am told I need to end up with this:
Case ID Date 1 Date 2 Date 3
000001 01/01/2010 02/01/2010 03/01/2010
000002 01/01/2011 02/01/2011 03/01/2011
Obviously proc transpose would do this easily if I only had one 'trio' for each Case ID. In some cases, however, there are multiple 'trios':
Case ID Date X High Low
0000003 01/01/2010 50 . 1
0000003 02/01/2010 672 1 .
0000003 03/01/2010 50 . 1
0000003 04/01/2010 50 . 1
0000003 05/01/2010 999 1 .
0000003 06/01/2010 50 . 1
0000004 01/01/2011 50 . 1
0000004 02/01/2011 100 1 .
0000004 03/01/2011 50 . 1
0000004 04/01/2011 50 . 1
0000004 05/01/2011 300 1 .
0000004 06/01/2011 50 . 1
In this instance, I would want my new table to look like:
Case ID Date 1 Date 2 Date 3
000003 01/01/2010 02/01/2010 03/01/2010
000003 04/01/2010 05/01/2010 06/01/2010
000004 01/01/2011 02/01/2011 03/01/2011
000004 04/01/2011 05/01/2011 06/01/2011
How can I do this?
It is easy.IF you make sure there are three observations in each group.
data x; input id dt : mmddyy. x high low; format id z7. dt mmddyy10.; datalines; 0000003 01/01/2010 50 . 1 0000003 02/01/2010 672 1 . 0000003 03/01/2010 50 . 1 0000003 04/01/2010 50 . 1 0000003 05/01/2010 999 1 . 0000003 06/01/2010 50 . 1 0000004 01/01/2011 50 . 1 0000004 02/01/2011 100 1 . 0000004 03/01/2011 50 . 1 0000004 04/01/2011 50 . 1 0000004 05/01/2011 300 1 . 0000004 06/01/2011 50 . 1 ; run; data x; set x; if mod(_n_,3)=1 then do;count=0;group+1;end; count+1; run; proc transpose data=x out=want(drop=group _name_) prefix=date; by id group; var dt; id count; run;
Ksharp
Thanks. This seems to be a step in the right direction, BUT I am left with this when I try to transpose the data:
Case ID Date Date1 Date2 Date3
0000003 01/01/2010 01/01/2010 . .
0000003 02/01/2010 . 02/01/2010 .
0000003 03/01/2010 . . 03/01/2010
0000003 04/01/2010 04/01/2010 . .
0000003 05/01/2010 . 05/01/2010 .
0000003 06/01/2010 . . 06/01/2010
0000004 01/01/2011 01/01/2011 . .
0000004 02/01/2011 . 02/01/2011 .
0000004 03/01/2011 . . 03/01/2011
0000004 04/01/2011 04/01/2011 . .
0000004 05/01/2011 . 05/01/2011 .
0000004 06/01/2011 . . 06/01/2011
That definitely ISN'T the result I get when I run KSharp's code. You must have changed something.
You're right, I referenced the wrong variable in my 'by' statement. I think I have what I want now. Thank you all.
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.
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.