Hi,
Apologies if this has been answered elsewhere. I am using SAS 9.4. trying to transpose my data from long to wide. I would like to create a new variable 'surgerynumber' to use in the id statement in proc transpose command.
My data is as:
ID | Surgery | date | Type | Complication | Histo | timepoint |
1 | Surgery | 2/06/2009 | resection | No | No residual macroscopic disease | Initial Diag |
2 | Surgery | 2/08/2013 | excision | Yes | No residual macroscopic disease | Before radio |
3 | Surgery | 24/10/2012 | resection | No | No residual macroscopic disease | Initial Diag |
4 | Surgery | 26/02/2014 | excision | No | No residual macroscopic disease | Before radio |
4 | Surgery | 26/07/1994 | excision | No | No residual macroscopic disease | Initial Diag |
5 | Surgery | 25/03/2011 | excision | No | No residual macroscopic disease | Initial Diag |
As you can see that out of the 5 cases, only number 4 had surgery twice. So I would like to create a new variable 'surgerynumber' such that for case 1,2,3,5 it will be surgerynumber=1 and for case 4 it will be surgerynumber=1 for the first surgery and surgerynumber=2 for the second surgery.
Would appreciate any solutions as to how to tackle the above - thanks
The code, applied to your original example data, works:
data have;
infile cards dlm=',';
input ID $ Surgery $ date :ddmmyy10. Type :$10. Complication $ Histo :$15. timepoint :$20.;
format date ddmmyy10.;
cards;
1,Surgery,2/06/2009,resection,No,No residual macroscopic disease,Initial Diag
2,Surgery,2/08/2013,excision,Yes,No residual macroscopic disease,Before radio
3,Surgery,24/10/2012,resection,No,No residual macroscopic disease,Initial Diag
4,Surgery,26/02/2014,excision,No,No residual macroscopic disease,Before radio
4,Surgery,26/07/1994,excision,No,No residual macroscopic disease,Initial Diag
5,Surgery,25/03/2011,excision,No,No residual macroscopic disease,Initial Diag
;
run;
data want;
set have;
by id;
if first.id
then surgerynumber = 1;
else surgerynumber + 1;
run;
proc print data=want noobs;
run;
Result:
ID Surgery date Type Complication Histo timepoint surgerynumber 1 Surgery 02/06/2009 resection No No residual mac Initial Diag 1 2 Surgery 02/08/2013 excision Yes No residual mac Before radio 1 3 Surgery 24/10/2012 resection No No residual mac Initial Diag 1 4 Surgery 26/02/2014 excision No No residual mac Before radio 1 4 Surgery 26/07/1994 excision No No residual mac Initial Diag 2 5 Surgery 25/03/2011 excision No No residual mac Initial Diag 1
You can see that surgerynumber = 2 is clearly there.
In a data step with
by id;
do
if first.id
then surgerynumber = 1;
else surgerynumber + 1;
The incrementation statement in the else branch will automatically retain the variable.
Thanks so much for your prompt reply, Kurt.
I've put the code in as:
Data surgery;
SET surgery;
by id;
if first.idthen surgerynumber = 1;
else surgerynumber + 1;
RUN;
PROC PRINT data='surgery';
RUN;
It works in that there is a new variable surgerynumber = 1 to every id but for the 2nd surgery it jumps to surgery=3 (not 2). I have a dataset of 330 cases and even for 1 case that had 3 surgeries, with the new variable surgerynumber=1,3,4 (ie misses the 2). Not sure how to rectify this? Also some cases had no surgery (in the surgery column, it is just blank) but a 1 is put down next to the case with surgerynumber. Can we refine?
Many thanks in advance
The code, applied to your original example data, works:
data have;
infile cards dlm=',';
input ID $ Surgery $ date :ddmmyy10. Type :$10. Complication $ Histo :$15. timepoint :$20.;
format date ddmmyy10.;
cards;
1,Surgery,2/06/2009,resection,No,No residual macroscopic disease,Initial Diag
2,Surgery,2/08/2013,excision,Yes,No residual macroscopic disease,Before radio
3,Surgery,24/10/2012,resection,No,No residual macroscopic disease,Initial Diag
4,Surgery,26/02/2014,excision,No,No residual macroscopic disease,Before radio
4,Surgery,26/07/1994,excision,No,No residual macroscopic disease,Initial Diag
5,Surgery,25/03/2011,excision,No,No residual macroscopic disease,Initial Diag
;
run;
data want;
set have;
by id;
if first.id
then surgerynumber = 1;
else surgerynumber + 1;
run;
proc print data=want noobs;
run;
Result:
ID Surgery date Type Complication Histo timepoint surgerynumber 1 Surgery 02/06/2009 resection No No residual mac Initial Diag 1 2 Surgery 02/08/2013 excision Yes No residual mac Before radio 1 3 Surgery 24/10/2012 resection No No residual mac Initial Diag 1 4 Surgery 26/02/2014 excision No No residual mac Before radio 1 4 Surgery 26/07/1994 excision No No residual mac Initial Diag 2 5 Surgery 25/03/2011 excision No No residual mac Initial Diag 1
You can see that surgerynumber = 2 is clearly there.
Thanks Kurt!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.