topic Re: How to create a new variable with long data in SAS Procedures

How to create a new variable with long data

lavienrose1 — Tue, 17 Apr 2018 04:29:06 GMT

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

Re: How to create a new variable with long data

Kurt_Bremser — Tue, 17 Apr 2018 04:40:40 GMT

In a data step with

by id;

if first.id
then surgerynumber = 1;
else surgerynumber + 1;

The incrementation statement in the else branch will automatically retain the variable.

Re: How to create a new variable with long data

lavienrose1 — Tue, 17 Apr 2018 05:00:33 GMT

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

Re: How to create a new variable with long data

Kurt_Bremser — Tue, 17 Apr 2018 06:05:03 GMT

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.

Re: How to create a new variable with long data

lavienrose1 — Tue, 17 Apr 2018 22:47:47 GMT

Thanks Kurt!