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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.