Creating new variables by subtracting existing variables using a macro

[pchappus]

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;

[RW9]

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.

[pchappus]

Here is my original dataset which is the long format.

VALUATION_DATE INC_MO YTD_PAIDS
6/30/2015 1/1/2012 500
6/30/2015 2/1/2012 300
9/30/2015 1/1/2012 800
9/30/2015 3/1/2012 400
12/31/2015 2/1/2012 1000
12/31/2015 3/1/2012 700

outx1 is the wide format and appears as such
INC_MO 6/30/2015 9/30/2015 12/31/2015
1/1/2012 500 800
2/1/2012 300 1000
3/1/2012 400 700


My ultimate goal is to somehow split the YTD paids into a quarterly paid amount by subtracting the amounts at each valuation date. I first tried transposing it and then ran into the problem of having dates as variable names. I'm going to review Kurk's post and see if I can try and do this using the long format.

Thanks,
Paul

[Kurt_Bremser]

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)?

[ballardw · Accepted solution]

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.

[pchappus]

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;

[Tom]

There is no FIRST.YEAR since YEAR is not in the BY statement. Use YEAR NE LAG(YEAR) instead.

[pchappus]

Thanks again Tom. Wasn't aware of the lag feature. That appears to take care of my problem.

Paul

[Kurt_Bremser]

year is not in the by statement, so there will not be the automatic first.year variable.

[pchappus]

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.

[Tom]

---

@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.

[pchappus]

I think I got it to almost work using the long structure.

The only issue is when there was no payment in the first quarter, but rather the first payment came in 2nd 3rd or 4th quarter. I need to check to see if the previous record has the same year. If it doesn't, then I want to take the YTD paid claims variable as the qtr paids,

[Tom]

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;