Skip to content
Advertisement

Using macros in SAS SQL

I am wondering of I can consolidate some code into one PROC SQL statement instead of several back-to-back SQL statements.

I am using macro to calculate the age of a participant at date of consent for a study, similar to what is provided here and here. I tried to troubleshoot the functionality of macros, and I calculated the total weight of a baby at birth in ounces using the %sum macro (which works great…). However, when trying to calculate age, the macros don’t work.

However, if I use the macros in a new SQL statement, it works fine.

The code below works:


Is there any way to consolidate it into a single statement? Something like:

Advertisement

Answer

If you want to use a field that was previously created in the same SQL statement, you need to use the calculated keyword. IE:

Calculated is only needed when there is an actual calculation – ie, for b_Dt. c_dt is only a rename of infcondt2, so you can use c_dt (or infcondt2) interchangeably, and cannot use CALCUALTED.

That said, you no longer need to adjust for day-of-month if you have 9.2 or sooner – look at the documnentation for INTCK. There is an optional argument (in 9.3 it’s called METHOD, I think 9.2 calls it something different) that allows you to force it to use a continuous month-concept rather than a discrete concept (counting first-of-the-months is discrete, for example, as the default used to be).

Also, I don’t understand the point of the DAY macro – not only are DAYs integers (so you can just subtract the two numbers using normal subtraction), but why are you subtracting the day/day like in %month? That’s to correct for the part-of-month, as I just discussed, and is not needed for days (it would yield a wrong answer where somedate < birth).

Example of correct code:

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement