topic Re: Add additional row for specific patient IDs after last recorded month in SAS Data Management

Add additional row for specific patient IDs after last recorded month

js1983 — Wed, 30 May 2018 12:50:16 GMT

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!

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

RW9 — Wed, 30 May 2018 13:35:27 GMT

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;

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

js1983 — Wed, 30 May 2018 14:27:03 GMT

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?

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

SuryaKiran — Wed, 30 May 2018 14:28:00 GMT

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;

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

RW9 — Wed, 30 May 2018 14:30:17 GMT

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-data-AKA-generate/ta-p/258712

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

js1983 — Thu, 31 May 2018 07:02:06 GMT

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

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

js1983 — Thu, 31 May 2018 08:07:26 GMT

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!

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

RW9 — Thu, 31 May 2018 08:12:39 GMT

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.

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

js1983 — Thu, 31 May 2018 08:30:04 GMT

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.

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

RW9 — Thu, 31 May 2018 08:55:05 GMT

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.

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

js1983 — Thu, 31 May 2018 08:56:48 GMT

Ok great, will do next time - thank!