BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lavienrose1
Calcite | Level 5

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:

IDSurgerydateTypeComplicationHistotimepoint
1Surgery2/06/2009resectionNoNo residual macroscopic diseaseInitial Diag
2Surgery2/08/2013excisionYesNo residual macroscopic diseaseBefore radio
3Surgery24/10/2012resectionNoNo residual macroscopic diseaseInitial Diag
4Surgery26/02/2014excisionNoNo residual macroscopic diseaseBefore radio
4Surgery26/07/1994excisionNoNo residual macroscopic diseaseInitial Diag
5Surgery25/03/2011excisionNoNo residual macroscopic diseaseInitial 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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

 

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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.

lavienrose1
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

 

lavienrose1
Calcite | Level 5

Thanks Kurt!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1103 views
  • 0 likes
  • 2 in conversation