topic Re: How to do a macro which could apply to multiple years and months? in Advanced Programming

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

wantonxia — Wed, 18 Nov 2020 16:35:29 GMT

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;

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

ballardw — Wed, 18 Nov 2020 16:47:43 GMT

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.

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

PaigeMiller — Wed, 18 Nov 2020 16:57:34 GMT

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

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

wantonxia — Wed, 18 Nov 2020 17:38:56 GMT

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!

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

Kurt_Bremser — Wed, 18 Nov 2020 17:42:50 GMT

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

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

wantonxia — Wed, 18 Nov 2020 17:55:27 GMT

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

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

ballardw — Wed, 18 Nov 2020 20:31:13 GMT

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-data-AKA-generate/ta-p/258712 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.

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

wantonxia — Wed, 18 Nov 2020 22:26:36 GMT

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!

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

ballardw — Wed, 18 Nov 2020 23:32:41 GMT

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.

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

wantonxia — Thu, 19 Nov 2020 00:30:01 GMT

Thank you so much ballardw! I am very appreciated!