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.

 

sas-innovate-2024.png

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.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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