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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1064 views
  • 0 likes
  • 3 in conversation