Add additional row for specific patient IDs after last recorded month

[js1983]

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!  

[RW9 · Accepted solution]

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;

[js1983]

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? 

[RW9]

You can use this in any SAS software:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[js1983]

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

[SuryaKiran]

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;

[js1983]

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!

[RW9]

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.

[js1983]

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. 

[RW9]

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.

[js1983]

Ok great, will do next time - thank!