Hello Everyone,
I am trying to create a macro for this calculation I am doing for several datasets but just can't seem to get my code to work. Here's some information you might need if you want to help me.
Library name: Diabetes
Datasets = FFYEAR09 FFYEAR10 FFYEAR11 FFYEAR12 FFYEAR13 FFYEAR14 FFYEAR15
Here is the original code I want to use in a macro; it works when I do it individually for a dataset.
DATA DIABETES.FFYEAR09 (KEEP=RECIPIENT_NBR ALLMEDS TOTALQNT OCTMEDS OCTQNT);
SET DIABETES.FFYEAR09;
BY RECIPIENT_NBR NDC;
RETAIN ALLMEDS TOTALQNT OCTMEDS OCTQNT;
IF FIRST.RECIPIENT_NBR THEN DO;
ALLMEDS = 0;
TOTALQNT = 0;
OCTMEDS = 0;
OCTQNT = 0;
END;
IF FIRST.NDC THEN DO;
ALLMEDS + 1;
TOTALQNT + RX_QUANTITY;
IF MONTH (RX_FILL_DT) = 10 THEN DO;
OCTMEDS + 1;
OCTQNT + RX_QUANTITY;
END;
END;
IF LAST.RECIPIENT_NBR THEN OUTPUT;
LABEL ALLMEDS = 'TOTAL NUMBER OF MEDICATIONS'
TOTALQNT = 'TOTAL QUANTITY OF PILLS'
OCTMEDS = 'TOTAL NUMBER OF MEDICATIONS IN OCTOBER'
OCTQNT = 'TOTAL QUANTITY OF PILLS IN OCTOBER';
RUN;
I am fairly new to the world of SAS and this is my first attempt at a macro. This is the macro I developed based on some information I found online for macro beginners.
%MACRO TOTALMEDS;
%DO I=1 %TO 7;
DATA = DIABETES.FFYEAR&I (KEEP=RECIPIENT_NBR ALLMEDS TOTALQNT OCTMEDS OCTQNT);
BY RECIPIENT_NBR NDC;
RETAIN ALLMEDS TOTALQNT OCTMEDS OCTQNT;
IF FIRST.RECIPIENT_NBR THEN DO;
ALLMEDS = 0;
TOTALQNT = 0;
OCTMEDS = 0;
OCTQNT = 0;
END;
IF FIRST.NDC THEN DO;
ALLMEDS + 1;
TOTALQNT + RX_QUANTITY;
IF MONTH (RX_FILL_DT) = 10 THEN DO;
OCTMEDS + 1;
OCTQNT + RX_QUANTITY;
END;
END;
IF LAST.RECIPIENT_NBR THEN OUTPUT;
LABEL ALLMEDS = 'TOTAL NUMBER OF MEDICATIONS'
TOTALQNT = 'TOTAL QUANTITY OF PILLS'
OCTMEDS = 'TOTAL NUMBER OF MEDICATIONS IN OCTOBER'
OCTQNT = 'TOTAL QUANTITY OF PILLS IN OCTOBER';
RUN;
%MEND TOTALMEDS;
MPRINT (TOTALMEDS): PROC PRINT DATA=DIABETES.FFYEAR&I;
%TOTALMEDS
So something tells me I got the first statement after the "%Do I=1 …" wrong, and I am not sure where "MPRINT" is supposed to go for SAS to tell me exactly where I am messing up. Is there a more complicated macro I'm supposed to use?
If anyone can please shed some light on this, I would greatly appreciate your help!
I am using SAS 9.2
Moreover, the SET statement is missing in your macro. For completeness (and good programming practice), I would also suggest to include a %LOCAL statement.
So, to put everything together, the macro should have the following structure:
%macro totalmeds;
%local i;
%do i=9 %to 15;
data diabetes.ffyear%sysfunc(putn(&i,z2.))(keep=recipient_nbr allmeds totalqnt octmeds octqnt);
set diabetes.ffyear%sysfunc(putn(&i,z2.));
by ...
...
run;
%end;
%mend totalmeds;
That said, it seems unusual to me that permanent datasets with detail data are overwritten with datasets containing only aggregated data. (I worked on a diabetes project.) But I assume you know what you're doing and the lost information is still available somewhere else.
If your data goes from 9 to 15 why are you looping from 1 to 7?
Loop from 9 to 15 and use z2. Format so that the 9 converts to 09.
On top of the solution to add %end.
There shouldn't be an equals sign in a DATA statement. Your original code doesn't use one, but somehow the macro added one:
DATA = DIABETES.FFYEAR&I (KEEP=RECIPIENT_NBR ALLMEDS TOTALQNT OCTMEDS OCTQNT);
Moreover, the SET statement is missing in your macro. For completeness (and good programming practice), I would also suggest to include a %LOCAL statement.
So, to put everything together, the macro should have the following structure:
%macro totalmeds;
%local i;
%do i=9 %to 15;
data diabetes.ffyear%sysfunc(putn(&i,z2.))(keep=recipient_nbr allmeds totalqnt octmeds octqnt);
set diabetes.ffyear%sysfunc(putn(&i,z2.));
by ...
...
run;
%end;
%mend totalmeds;
That said, it seems unusual to me that permanent datasets with detail data are overwritten with datasets containing only aggregated data. (I worked on a diabetes project.) But I assume you know what you're doing and the lost information is still available somewhere else.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.