Help using Base SAS procedures

How to create a new variable for matched controls

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

How to create a new variable for matched controls

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!


Accepted Solutions
Solution
‎04-07-2014 03:07 PM
Contributor
Posts: 28

Re: How to create a new variable for matched controls

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.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How to create a new variable for matched controls

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.

Contributor
Posts: 28

Re: How to create a new variable for matched controls

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!

Contributor
Posts: 29

Re: How to create a new variable for matched controls

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.

Contributor
Posts: 28

Re: How to create a new variable for matched controls

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.

Solution
‎04-07-2014 03:07 PM
Contributor
Posts: 28

Re: How to create a new variable for matched controls

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.

Respected Advisor
Posts: 3,156

Re: How to create a new variable for matched controls

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

Respected Advisor
Posts: 3,156

Re: How to create a new variable for matched controls

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 262 views
  • 0 likes
  • 4 in conversation