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

I am working on a case-crossover study which consists of cases and controls, where the cases act as their own controls but at different times than when the outcome occurred. I have a dataset consisting of only cases and need to generate multiple controls for each case. The controls need to be on the same day of the week, within the same month and year as the case. For example, if I had a case occurring on 1/01/2005 I would need to generate a control for 1/08/2005, 1/15/2005, 1/22/2005, and 1/29/2005. This case happened on a Saturday so all the controls would be on every other Saturday of January. Sometimes, there will be only three controls, i.e. the case happened on 1/18/2005 so I would need controls for 1/04/2005, 1/11/2005, and 1/25/2005. I have cases ranging from 1/01/2005 to 12/31/2010. This process would need to be repeated for all dates between that range.

 

I have attached a subset of observations in a tab-delimited text file. Thank you so much for your help! 

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

See if this is close to what you are asking for.  Not too elegant, but it at least satisfies the two cases that you mention.

Data have;
input case casedate :mmddyy10.;
datalines;
1 01/01/2005
2 01/18/2005
run;

* Want for a given case:
* control dates for all the same DOW in the same month;
data want;
   set have;
   * Control dates can be up to 4 weeks prior and 4 weeks after the casedate;
   do i = -4 to 4;
      controldate = intnx('week',casedate,i,'s');
      if month(casedate)=month(controldate) and 
          controldate ne casedate then output want;
   end;
   format casedate controldate date9.;
   run;
proc print data=want;
run;

For a given CASEDATE the INTNX function will 'advance' the date some number of weeks into the future (or past).  The 'Same' alignment option keeps the same day of week.

 

Result:

 

 Obs    case     casedate     i    controldate

  1       1     01JAN2005     1     08JAN2005 
  2       1     01JAN2005     2     15JAN2005 
  3       1     01JAN2005     3     22JAN2005 
  4       1     01JAN2005     4     29JAN2005 
  5       2     18JAN2005    -2     04JAN2005 
  6       2     18JAN2005    -1     11JAN2005 
  7       2     18JAN2005     1     25JAN2005 

View solution in original post

3 REPLIES 3
ArtC
Rhodochrosite | Level 12
Could you please reattach the data file. or post it.
ArtC
Rhodochrosite | Level 12

See if this is close to what you are asking for.  Not too elegant, but it at least satisfies the two cases that you mention.

Data have;
input case casedate :mmddyy10.;
datalines;
1 01/01/2005
2 01/18/2005
run;

* Want for a given case:
* control dates for all the same DOW in the same month;
data want;
   set have;
   * Control dates can be up to 4 weeks prior and 4 weeks after the casedate;
   do i = -4 to 4;
      controldate = intnx('week',casedate,i,'s');
      if month(casedate)=month(controldate) and 
          controldate ne casedate then output want;
   end;
   format casedate controldate date9.;
   run;
proc print data=want;
run;

For a given CASEDATE the INTNX function will 'advance' the date some number of weeks into the future (or past).  The 'Same' alignment option keeps the same day of week.

 

Result:

 

 Obs    case     casedate     i    controldate

  1       1     01JAN2005     1     08JAN2005 
  2       1     01JAN2005     2     15JAN2005 
  3       1     01JAN2005     3     22JAN2005 
  4       1     01JAN2005     4     29JAN2005 
  5       2     18JAN2005    -2     04JAN2005 
  6       2     18JAN2005    -1     11JAN2005 
  7       2     18JAN2005     1     25JAN2005 
SMcelroy1287
Obsidian | Level 7

Thank you very much for your help! I'm sorry the file did not upload correctly but I was able to modify your code to fit the data!

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2935 views
  • 2 likes
  • 2 in conversation