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!
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.
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.
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!
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.
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.
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.
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.