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!
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;
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;
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?
You can use this in any SAS software:
Great, thank you for the link! Will test right now
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;
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!
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.
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.
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.
Ok great, will do next time - thank!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.