calculate difference between two lists of variables

[garfield83]

I have two list of variables, they are the same metrics, but from different point of time, right now I want to calculate the difference between them, the dataset looks like this:

account fico_201701 beacon_201701 dpd_201701 fico_201712 beacon_201712 dpd_201712

I want to calculate difference between fico_201701 and fico_201712/ beacon_201701 and beacon_201712 and etc, how do I program for it?

Thanks!

[SuryaKiran]

Are you joining two tables into one and trying to get the difference between variables of alike ones.

Like :

Table A : account fico_201701 beacon_201701 dpd_201701
Table B : account fico_201712 beacon_201712 dpd_201712

[garfield83]

Exactly right

[Satish_Parida]

1. Sort the 2 datasets by account.
2. Merge the 2 datasets to create a single dataset by account.
3. In the Merge step subtract the related variables to get your result.
e.g. fico_diff=fico_201701-fico_201712

[andreas_lds]

I would start by fixing the input datasets so that they have a date-variable containing the postfix of the other variables, thus you can store the data of any time point in on dataset. For the calculation you need the data sorted by account and date, then you can use the dif-function to get the differences.

[garfield83]

Right, but my question is how to do that using macro? I have 600 variables.

[andreas_lds]

Maybe my eyes need to checked by a doc, but i can't see anything about having 600 vars in your starting post. Using an array with a simple loop seems to be appropriate.

Untested fragment:

data work.want;
	set have;
	by account;

	array vars fico beacon dpd ...;
	array diffs fico_diff beacon_diff dpd_diff ...;

	do i = 1 to dim(vars);
		diffs[i] = lag(vars[i]);
		if first.account then do;
			diffs[i] = 0;
		end;
	end;
run;