How to do a macro which could apply to multiple years and months?

[wantonxia]

Hi All,

I am working on a project. If a patient had an event in Jan 2016, I need to make sure this patient did not have another indicator within 6 months prior to Jan 2016. Wondering how to create a macro which could be applied to multiple years and months? For example, events from Jan 2016 to Dec 2018?

Thanks!

My codes:

  if event_month = 1 and event_year = 2016 then do;

  /* drop if indicator not in 0 */

  if indicator12_2015 not in ("0") then drop = 1;

  if indicator11_2015 not in ("0") then drop = 1;

  if indicator10_2015 not in ("0") then drop = 1;

  if indicator9_2015 not in ("0") then drop = 1;

  if indicator8_2015 not in ("0") then drop = 1;

  if indicator7_2015 not in ("0") then drop = 1;

  end;

  if drop = 1 then delete;

[ballardw]

Since you have data in the variable name for all those indicator variables then your data structure is likely making this much harder than it should be.

You would be better off with a structure that has a variable named "date of event", with an actual date value and one record per person event. Then this becomes fairly easy. SAS has functions to compare date intervals directly.

With that suggested structure you could identify records within 6 months with something like

if intck('month',dateofevent, "01JAN2016"d) le 6 then <do whatever>.

A "macro" would then allow you dynamically replace the reference date and the number of months very easily.

Your current structure would be a moving target of variable names and ugly ones at that.

[PaigeMiller]

Adding to what @ballardw said, you probably could transpose the data somehow to a much more agreeable format, and then the coding is simple.

[wantonxia]

Agree. I just updated my question. Thank you for your help!

[wantonxia]

Hi ballardw, 

Thank you so much for your help! It makes sense. Could I ask one more questions please?

I got it that I'd better to use dateofevent and intck('month',dateofevent, "01JAN2016"d) le 6. However, I am not sure how to use the monthly indicator instead of "01JAN2016"d.

Below is the further clarification of my data. Actually, in my data, it's not like indicator12_2015 or indicator11_2015. The format is indicator 1- indicator12 for each month (indication 1 is Jan, and indicator 12 is Dec) of each year (dummy variable, 1=yes, and 0=No). In addition, I do have the date of event. My goal is to measure whether the patients had '0' in each monthly indicator within 6 months prior to the date of event. 

Please let me know if I did not explain it clearly and happy to clarify.

Thanks again!

[Kurt_Bremser]

So each observation covers a whole year? Is that year stored in another variable in that observation?

[ballardw]

Data

Actual example data.

I'm not sure that your description makes anything better.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[wantonxia]

Hi ballardw,

Sorry it was my first time to post a question and I did not know it. Thanks for your suggestion. Below is the datasets.

/* Year 2017 */
data work2017;
input patientid $4. indicator_month1-indicator_month12 eventdate mmddyy10.;
format eventdate mmddyy10.;
datalines;
001 0 0 0 0 0 0 0 0 0 0 0 0 03/19/2018
002 0 1 1 1 1 1 1 1 1 1 1 1 09/01/2018
003 0 0 0 0 0 0 0 0 0 0 0 0 02/20/2019
004 0 0 0 0 0 0 1 1 1 1 1 1 12/12/2019
;
run;

/* Year 2018 */
data work2018;
input patientid $4. indicator_month1-indicator_month12 eventdate mmddyy10.;
format eventdate mmddyy10.;
datalines;
001 0 0 0 1 1 1 1 1 1 1 1 1 03/19/2018
002 1 1 1 1 1 1 1 1 1 1 1 1 09/01/2018
003 0 0 0 0 0 0 0 0 0 0 1 1 02/20/2019
004 1 1 1 1 1 1 1 1 1 1 1 1 12/12/2019
;
run;

/* Year 2019 */
data work2019;
input patientid $4. indicator_month1-indicator_month12 eventdate mmddyy10.;
format eventdate mmddyy10.;
datalines;
001 1 1 1 1 1 1 1 1 1 1 0 0 03/19/2018
002 1 1 1 1 1 1 1 1 1 1 1 1 09/01/2018
003 0 1 1 1 1 1 1 1 0 0 0 0 02/20/2019
004 1 1 1 1 0 0 0 0 0 0 0 0 12/12/2019
;
run;

I have three years of data. It includes patient ID, date of event, and indicator month. In each year, indicator month is from 1-12, which represents Jan to Dec of this year. My goal is: if a patient had at least a indicator value=1 within 6 month prior to the event month, this patient will be excluded. For example, Patient 003's event date was 02/20/2019,  I need to find the value of indicator_month from August 2018 to Jan 2019, and found this patient had indicator_month11=1 and indicator_month_12=1 in 2018 and indicator_month_1 =1 in 2019. So this patient should be excluded. 

Please let me know if I am not explain it clearly and happy to further clarify it. Thanks again!

[ballardw · Accepted solution]

That data structure is hiding two critical pieces of data: the year and the month of a whatever the indicator means.

You may be better off with data such as

data work2018;
input patientid $4. indicator_month1-indicator_month12 eventdate mmddyy10.;
format eventdate mmddyy10.;
datalines;
001 0 0 0 1 1 1 1 1 1 1 1 1 03/19/2018
002 1 1 1 1 1 1 1 1 1 1 1 1 09/01/2018
003 0 0 0 0 0 0 0 0 0 0 1 1 02/20/2019
004 1 1 1 1 1 1 1 1 1 1 1 1 12/12/2019
;
run;

data need;
   set work2018;
   array ind (*) indicator_: ;
   do i=1 to dim(ind);
      if ind[i]=1 then do;
        indicatordate= mdy(i,1,2018);
        output;
      end;

   end;
   format indicatordate mmddyy10.;
   keep patientid eventdate indicatordate;
run;

 data want;
    set need;
    if intck ('month',indicatordate,eventdate) le 6;
run;

So now you can use Indicatordate along with any other date of interest to calculate intervals, duration or keep/exclude. To work across years make "need" data sets from each of the year data sets and then combine them.

The above code finds all the "indicator months" within the given range of 6 months. Up to you to use that to "exclude" or do anything else.

[wantonxia]

Thank you so much ballardw! I am very appreciated!