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!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: