Help using Base SAS procedures

How to create a new variable with long data

Accepted Solution Solved
Reply
Highlighted
Occasional Contributor
Posts: 9
Accepted Solution

How to create a new variable with long data

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


Accepted Solutions
Solution
Tuesday
Super User
Posts: 9,300

Re: How to create a new variable with long data

Posted in reply to lavienrose1

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,300

Re: How to create a new variable with long data

Posted in reply to lavienrose1

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: How to create a new variable with long data

Posted in reply to KurtBremser

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

Solution
Tuesday
Super User
Posts: 9,300

Re: How to create a new variable with long data

Posted in reply to lavienrose1

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: How to create a new variable with long data

Posted in reply to KurtBremser

Thanks Kurt!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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