My issue is I have YTD claims paid and need claims paid by quarter. The data is loaded every quarter so I've transposed the data so that every load date (which is every quarter) is a new variable.
Now I'm trying to loop through a create a new column for each quarter. I'm having an issue of the column being created using the macros. If I manually create a column using the below statement it works:
Y2012Q3 = '30Sep2013'n - '30Jun2013'n;
When I create my macro to do all 24 quarters, it doesn't work even though it appears it is resolving to the above statement.
Here is the code:
%MACRO QTR();
DATA out2;
SET work.outx1;
%LET startdate = %SYSFUNC(MDY(6,30,2012));
%DO i=0 %to 23;
%LET date1= %SYSFUNC(INTNX(MONTH,&StartDate,%EVAL(&i.*3)));
%LET date2= %SYSFUNC(INTNX(MONTH,&StartDate,%EVAL((&i.+1)*3)));
%LET datef1= %SYSFUNC(PUTN(%SYSFUNC(INTNX(MONTH,&StartDate,%EVAL(&i.*3))),date9.));
%LET datef2= %SYSFUNC(PUTN(%SYSFUNC(INTNX(MONTH,&StartDate,%EVAL((&i.+1)*3))),date9.));
%LET column = Y%SYSFUNC(PUTN(&date2.,YYQ.));
%PUT &=column;
&column. = %STR(%')&datef2.%STR(%'n) - %STR(%')&datef1.%STR(%'n);
%END
RUN;
%MEND;
OPTIONS MPRINT;
%QTR();
OPTIONS NOMPRINT;
Here's a brief example of what @RW9 means by relatively easy to work with in long form.
data example; input id $ period value; datalines; a 1 123 a 2 222 a 3 168 a 4 221 b 1 555 b 2 666 b 3 543 b 4 314 ; run; data want; set example; by id period; dv= dif(value); if first.id then valdif=.; /*or 0, which ever makes sense*/ else valdif=dv; drop dv; run;
The data should be sorted by identification variables and the time variable(s). Since your value difference likely should not be calculated across identification we use the first. not to assign the value. The DIF function returns the value of the current value of a variable minus that of the variable on the previous record.
There are some details about how the function interacts with conditionals that make it sensible to calculate for every record and conditionally keep the value.
I would recommend you do not transpose. Long data is much simpler to work with. Just from what you have posted you will have named literals as variable names (the " "n type syntax) which will make your coding life a lot harder. Then you don't really have a coding way of knowing what the date of the column is except for parsing variable name. Far better to keep data in the data part, and have variable names that are easy to work with.
Right now we do not have an idea (at least I don't) of what is in dataset outx1.
Could you please post an example for that (data step with datalines, so we can quickly create your dataset for testing)?
Here's a brief example of what @RW9 means by relatively easy to work with in long form.
data example; input id $ period value; datalines; a 1 123 a 2 222 a 3 168 a 4 221 b 1 555 b 2 666 b 3 543 b 4 314 ; run; data want; set example; by id period; dv= dif(value); if first.id then valdif=.; /*or 0, which ever makes sense*/ else valdif=dv; drop dv; run;
The data should be sorted by identification variables and the time variable(s). Since your value difference likely should not be calculated across identification we use the first. not to assign the value. The DIF function returns the value of the current value of a variable minus that of the variable on the previous record.
There are some details about how the function interacts with conditionals that make it sensible to calculate for every record and conditionally keep the value.
Can somebody explain why this code doesn't work? I think the first if statement isn't doing what I would expect. What I'm attempting to do with that first if statement is for the first instance of each incurred_mo and year combination, I want it set to the YTD variable.
DATA want;
SET work.WNIC_Temp;
BY INCURRED_MO VALUATION_DATE;
FORMAT QTR_Paids comma20.2;
year=year(valuation_date);
dv=dif(YTD_PAID_CLAIMS);
IF first.incurred_mo AND first.year THEN
QTR_Paids = YTD_PAID_CLAIMS;
ELSE IF first.INCURRED_MO THEN
QTR_Paids = YTD_PAID_CLAIMS;
ELSE
QTR_Paids = dv;
RUN;
Thanks again Tom. Wasn't aware of the lag feature. That appears to take care of my problem.
Paul
year is not in the by statement, so there will not be the automatic first.year variable.
I now see my macro is actually wrong. I would need to put in a condition that checks to see if date2 is the first quarter and if so, take that value instead of subtracting Q1 from Q4.
@pchappus wrote:
I now see my macro is actually wrong. I would need to put in a condition that checks to see if date2 is the first quarter and if so, take that value instead of subtracting Q1 from Q4.
So use the vertical structure and process by ID and YEAR.
Don't use macro logic to do something that the SAS language already supports. It will just make your code hard to understand and debug.
If you want to process a series of variables then use arrays.
array values '30JUN2013'n '30SEP2013'n .... ;
array diffs Y2013Q2 Y2013Q3 ... ;
do i=1 to dim(values-1);
diffs(i) = values(i+1) - values(i);
end;
You might want to create a macro to generate the variable names into macro variables.
%macro gen_names(n,startdate=%sysfunc(mdy(6,30,2013)));
%local i ;
%global list1 list2;
%let list1=;
%let list2=;
%do i=0 %to &n-1;
%let list1=&list1. Y%sysfunc(intnx(qtr,&startdate,&i),yyq6);
%let list2=&list2. "%sysfunc(intnx(qtr,&startdate,&i,e),date9)"d;
%end;
%mend;
%gen_names(4);
%put &=list1;
%put &=list2;
638 %put &=list1; LIST1=Y2013Q2 Y2013Q3 Y2013Q4 Y2014Q1 639 %put &=list2; LIST2="30JUN2013"d "30SEP2013"d "31DEC2013"d "31MAR2014"d
And then just use the macro variables to define your arrays.
array diffs &list1 ;
array values &list2;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: