topic Re: Macro with to do loop - index date interval time in SAS Programming

Macro with to do loop - index date interval time

Schtroumpfette — Wed, 17 Mar 2021 20:43:05 GMT

Dear SAS users,

I need your help! I would have to do a macro do loop and print the indext_date and an interval for each index time for each patient (-30 days and +30 days).

The patients in this table are unique and there is only one encounter per patient.

Based on this I will have to match to controls using another macro.

Can you please provide me with a code for the macro with do loops?

Here is an example of the table:

Encounter_id	hospital_id	Age	patient_id	gender	INDEX_date	Diabetes_event
2175	66	55	1	F	14611	1
4271	66	57	2	F	14619	1
2016	19	54	3	M	14620	1
2815	19	35	4	M	14621	1
5546	66	60	5	M	14629	1
4456	20	62	6	F	14644	1

Thanks a lot!

Re: Macro with to do loop - index date interval time

Reeza — Wed, 17 Mar 2021 21:06:02 GMT

I don't know that you need a macro. It would definitely help if you could explain what you're doing with a small data example. For starters, I don't think you need to do this to merge/join your data, you can do this type of join directly in SQL in the join clause.

I can see at least two interpretations of your question, one is to show the interval start and end and the other is to create an entry for each date in that interval. Both are shown below.

data want;
set have;

*figure out start/end of each interval;
interval_start = index_date - 30;
interval_end = index_date + 30;

*if you want a record for every date;
do date = interval_start to interval_end;
    output;
end;

run;

@Schtroumpfette wrote:

Dear SAS users,

I need your help! I would have to do a macro do loop and print the indext_date and an interval for each index time for each patient (-30 days and +30 days).

The patients in this table are unique and there is only one encounter per patient.

Based on this I will have to match to controls using another macro.

Can you please provide me with a code for the macro with do loops?

Here is an example of the table:

Encounter_id hospital_id Age patient_id gender INDEX_date Diabetes_event

2175 66 55 1 F 14611 1

4271 66 57 2 F 14619 1

2016 19 54 3 M 14620 1

2815 19 35 4 M 14621 1

5546 66 60 5 M 14629 1

4456 20 62 6 F 14644 1

Thanks a lot!

Re: Macro with to do loop - index date interval time

PaigeMiller — Wed, 17 Mar 2021 21:06:09 GMT

No macro needed. You can do this in a data step (if I am understanding you properly)

/* UNTESTED CODE */
data want;
    set have;
    index_date=index_date-30;
    output;
    index_date=index_date+60;
    output;
    format index_date mmddyy10.;
run;
proc print data=want;
run;

If you want tested code, please provide data in this format.

If that's not what you want, explain further and include examples of the desired output.

Re: Macro with to do loop - index date interval time

Reeza — Wed, 17 Mar 2021 21:10:38 GMT

Three different interpretations of your question so far 🙂

Re: Macro with to do loop - index date interval time

Schtroumpfette — Wed, 17 Mar 2021 21:27:11 GMT

Thank you both,

This is helpful. It would be to show the interval start and end.

The reason I need a macro is that I will need to create a macro to select controls from another dataset (risk set sampling using macro), so I thought I start with a small dataset first.

The dataset here is a small sample of the cases. I will have another dataset for controls, where I need to match on the index date interval, age(+/ 2 years), gender and hospital id.

I can provide an example of the control dataset. Is there a macro code for risk set sampling?

Does this make sense?

Thanks,

Nisrine

Thanks,

Re: Macro with to do loop - index date interval time

Schtroumpfette — Wed, 17 Mar 2021 21:27:38 GMT

Thank you, this is helpful.

Re: Macro with to do loop - index date interval time

Reeza — Thu, 18 Mar 2021 01:04:10 GMT

Go read up on PROC PSMATCH or Google CDC "greedy matching macro". No need to reinvent the wheel.

Also, you can do this via SQL. If you really want a macro, first you need base code that works. Do you have that?

Re: Macro with to do loop - index date interval time

Schtroumpfette — Thu, 18 Mar 2021 01:51:28 GMT

Thanks Reeza,

I don't have a base code but I am working on potentially something based on this: 152-30: SAS® Programs to Select Controls for Matched Case-Control Studies

It is risk set sampling matching macro for Case Control studies. Have you ever used this?

We opted out of PS matching.

I hope this helps.

Re: Macro with to do loop - index date interval time

PaigeMiller — Thu, 18 Mar 2021 10:43:27 GMT

@Schtroumpfette wrote:

The reason I need a macro is that I will need to create a macro to select controls from another dataset (risk set sampling using macro), so I thought I start with a small dataset first.

As I understand things, no macro is needed. However, you seem to be avoiding giving us details about what this macro is supposed to do and how it related to the -30 days and +30 days you discussed earlier. Do you want to select controls withing this –30 days and +30 days window? Do you want to select ALL controls for a patient within this time period, or the FIRST control within this time period, or something else? Please explain.

Re: Macro with to do loop - index date interval time

Schtroumpfette — Thu, 18 Mar 2021 13:02:05 GMT

Hello Paige,

Yes, correct, I have a dataset with the cases. I will also have a large dataset for controls which includes the cases. I want to select controls within -30 to +30 days window of the index case of the date. It is a large dataset and if I do this step first, it will be very helpful in terms of future processing and this way I am matching also on calendar time. Once I select these controls, I will then match on age+/2 years and gender and hospital_id.
1. I want to select all controls within this time period
2. Match on age =/- 2 years, same gender and same hospital id
3. a control can become a case
4. A control id is not equal to case id not the same person
5. There will be an iterative loop that cycles through the first observation to the last observation in the case dataset
6. keep only four controls in dataset.
7. append all controls to case dataset.

Does that help?
Thanks