BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
js1983
Calcite | Level 5

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!  

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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? 

js1983
Calcite | Level 5

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

 

SuryaKiran
Meteorite | Level 14

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
js1983
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Ok great, will do next time - thank!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

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.

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