topic How to create a new variable for matched controls in SAS Procedures

How to create a new variable for matched controls

Lefty — Thu, 03 Apr 2014 19:10:07 GMT

Hi, I have a cohort of patients who saw their doctors for back pain. Some patients got x-rays (cases) and some did not (controls). I used the one to many match macro (http://www2.sas.com/proceedings/sugi29/165-29.pdf), to create a dataset of matched cases and controls and includes the match_1 variable that's the same for each case and each control so I can identify pairs. I'm wondering if the patients who got x-rays had more doctor's visits during the follow-up period (by definition, all cases and controls were followed for 1 year after their first visit) compared to controls. I want to begin counting doctor's visits on the day of the x-ray for cases and on the analogous number of days since the index visit for controls so that their follow-up times are the same. It's pretty easy for cases to identify the "start day" as the day of their x-ray, but I'm not sure how to tell SAS to create a variable for the controls that is equal to the "start day" for each pair's case. I'm not too familiar with proc SQL but it seems like that might be the way to go?

Thanks so much in advance for any guidance!

Re: How to create a new variable for matched controls

ballardw — Thu, 03 Apr 2014 22:31:11 GMT

It will help if you can provide some lines of dummy data to indicate what you have and what you want the output to look like. Make sure you have at least one of the not-obvious combinations of data to work with.

Re: How to create a new variable for matched controls

Lefty — Thu, 03 Apr 2014 23:01:35 GMT

Thanks for the suggestion! Here's a table of what I have:

Match_1	X-ray	Day of xray
1	1	10
1	0	--
2	1	15
2	0	--

And what I would like is for SAS to create a new variable, Generic_day, that looks like this:

Match_1	X-ray	Day of xray	Generic_day
1	1	10	10
1	0	--	10
2	1	15	15
2	0	--	15

So that each matched control has an "anchor" date that is the same as the day the cases received x-rays.

Hope that helps and thanks again in advance!

Re: How to create a new variable for matched controls

Chrishi — Fri, 04 Apr 2014 06:17:35 GMT

you can use retain statement to get the result for the above problem.

data bug;

retain generic_day;

set xray;

if x-ray=1 then generic_day=day of xray;

run;

hope this works. if not please elobrate the problem you are facing.

Re: How to create a new variable for matched controls

Lefty — Mon, 07 Apr 2014 18:05:17 GMT

Thanks for your response. No, that doesn't work, the result when I do that is:

Match_1	X-ray	Day of xray	Generic_day
1	1	10	10
1	0	--	--
2	1	15	15
2	0	--	--

And generic_day is still missing for people who did not have x-rays. I must be misunderstanding the retain statement?

Thanks again for any advice.

Re: How to create a new variable for matched controls

Lefty — Mon, 07 Apr 2014 19:07:26 GMT

Ah, I tried a few other things and finally got it to work by using this:

data bug;

set xray;

by match_1;

retain Day_of_xray Generic_day;

if x-ray=1 then Generic_day=Day_of_xray; run;

I think the key thing is retaining the generic_day variable as well as the day_of_xray variable.

Thanks.

Re: How to create a new variable for matched controls

Haikuo — Mon, 07 Apr 2014 19:19:09 GMT

If your data is laid out exactly as you have shown here (eg. sorted by ascending match_1 and descending x-ray), @chrishi 's code will work. If not, either you need to sort the data before implementing @chrishi's code or opt for Proc SQL:

PROC SQL;

CREATE TABLE WANT AS

SELECT *, MAX(DAYSOFXRAY) AS GENERIC_DAY FROM HAVE GROUP BY MATCH_1;

QUIT;

Haikuo

Re: How to create a new variable for matched controls

Haikuo — Mon, 07 Apr 2014 19:22:44 GMT

what is the point of retaining a data set variable such as "Day_of_xray" if it is overridden each time new data is read in (they are automatically retained anyway)? And why "by match_1" if you are NOT using any of "first., last." variables (at least you haven't shown it in your code). Are we seeing the whole pictures here?

Haikuo