SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Add additional row for specific patient IDs after last recorded month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Add additional row for specific patient IDs after last recorded month

Hi There, 

 

I have a dataset with many rows per patient ID, whereby each row represents a month of follow up in a cohort study. To run a weighted regression model I need to add an extra month after the last month of follow up for all patients with CENS_REASON='changeexp' or 'switch'. In this additional row the outcome should be set to missing (.) and exposure becomes 0 in case of 'changeexp' and the alternate of the prior number for 'switch' i.e. 1 becomes 2 and 2 becomes 1, month becomes ('last month'+1) and PatID should be carried down - all other covariates can be missing. 

 

The file has many more covariates and varying lab values over time in several thousand observations so I need to do that automated somehow,  but I don't know how to go about it (consequently I haven't tried anything yet). Any help is appreciated! 

I am working with SAS 9.4.

 

My file has the following structure (many other covariates that are not listed too): 

 

PatID   exposure   month    cens_reason         outcome

 

1               2              0             switch                   0

1               2              1             switch                   0

1               2              2             switch                   0

2               1              0             death                    0

2               1             1              death                    0

2               1             2             death                     1

3               2             0             changeexp             0

3               2             1             changeexp             0

2               2             2             changeexp             0

3               2             3             changeexp             0

 

And I need it to have the following structure 

 

PatID   exposure   month    cens_reason         outcome

 

1               2              0             switch                   0

1               2              1             switch                   0

1               2              2             switch                   0

1               1              3             .                   .

2               1              0             death                    0

2               1             1              death                    0

2               1             2             death                     1

3               2             0             changeexp            0

3               2             1             changeexp            0

2               2             2             changeexp            0

3               2             3             changeexp            0

3               0             4             .            .

 

 

Thanks for any help!  

 


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,397

Re: Add additional row for specific patient IDs after last recorded month

Not here to type that test data in, post test data in the form of a datastep, as such this is just a guide:

data want;
  set have;
  by cens_reason;
  if last.cens_reason and cens_reason in ('switch','changeexp') then do;
    output;
    month=month+1;
    output;    
  end;
  else output;
run;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 9,397

Re: Add additional row for specific patient IDs after last recorded month

Not here to type that test data in, post test data in the form of a datastep, as such this is just a guide:

data want;
  set have;
  by cens_reason;
  if last.cens_reason and cens_reason in ('switch','changeexp') then do;
    output;
    month=month+1;
    output;    
  end;
  else output;
run;
Occasional Contributor
Posts: 10

Re: Add additional row for specific patient IDs after last recorded month

Thank you so much that worked perfectly! I just slightly changed it to: 

 

data temp.marginal2;
  set temp.marginal1;
  by USRDS_ID month;
  if last.USRDS_ID and at_death_reason in ('switch','changeexp') then do;
    output;
    month=month+1;    
	outcome=.;
	output; 
  end;
  else output;
run;

 

I wasn't sure how to create a sample data set if I dont work with the SAS University Edition - any link for instructions for next time? 

Super User
Super User
Posts: 9,397

Re: Add additional row for specific patient IDs after last recorded month

Occasional Contributor
Posts: 10

Re: Add additional row for specific patient IDs after last recorded month

Great, thank you for the link! Will test right now

 

Valued Guide
Posts: 558

Re: Add additional row for specific patient IDs after last recorded month

Please provide data in the form of datastep check here how.

 

Looks like your 9th records must have patid 3 instead of 2.

 

data have;
input PatID   exposure   month    cens_reason   :$15.      outcome;
datalines;
1 2 0 switch 0
1 2 1 switch 0
1 2 2 switch 0
2 1 0 death 0
2 1 1 death 0
2 1 2 death 1
3 2 0 changeexp 0
3 2 1 changeexp 0
3 2 2 changeexp 0
3 2 3 changeexp 0
;
run;

proc sort data=have;
by PatID exposure month cens_reason;
run;

data want ;
set have;
by PatID;
if last.patid and cens_reason in ("changeexp","switch") then do;
output;
month=month+1;
if cens_reason="switch" then exposure=exposure-1;
else exposure=0;
call missing(cens_reason,outcome);
output;
end;
else output;
run;
Thanks,
Suryakiran
Occasional Contributor
Posts: 10

Re: Add additional row for specific patient IDs after last recorded month

Posted in reply to SuryaKiran

You are right - thanks for pointing this out. I did manage to create a test data set with the macro from the link - thanks will use that for future posts! However, I  tried to attach the file (test.sas) via the 'choose files' button but it wont let me upload it but I get the error message saying 'the contents of the attachment doesn't match its file type' - any recommendations on what I am doing wrong? 

 

Your code however worked perfectly -so no need to look into it for now. Thanks so much for the quick help!

 

 

Super User
Super User
Posts: 9,397

Re: Add additional row for specific patient IDs after last recorded month

Best way is to copy + paste the text and post it in a code window.  The code window is opened by using the {i} above the post area - this retains code formatting and such like.  Note we only need a few rows of test data just to show structure and some example of the data.

Occasional Contributor
Posts: 10

Re: Add additional row for specific patient IDs after last recorded month

I get that for the code, but if I want to attach a dataset I will need to do it differently no? I ran the macro to create some test data for my question - but I cant attach it. 

Super User
Super User
Posts: 9,397

Re: Add additional row for specific patient IDs after last recorded month

Paste test data as text, e.g.:

data have; 
  input...
datalines;
...
;
run;

You can attach files (make sure the extension matches, so you said it was a .sas file which is text, but talk about a datafile, which is sas7bdat).  However I would always advise to post as text, most will not download files from the internet.

Occasional Contributor
Posts: 10

Re: Add additional row for specific patient IDs after last recorded month

Ok great, will do next time - thank!

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 156 views
  • 0 likes
  • 3 in conversation