topic Re: calculate difference between two lists of variables in SAS Programming

calculate difference between two lists of variables

garfield83 — Tue, 06 Mar 2018 20:01:25 GMT

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!

Re: calculate difference between two lists of variables

SuryaKiran — Tue, 06 Mar 2018 20:16:32 GMT

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

Re: calculate difference between two lists of variables

garfield83 — Wed, 07 Mar 2018 02:49:14 GMT

Exactly right

Re: calculate difference between two lists of variables

Satish_Parida — Wed, 07 Mar 2018 04:23:12 GMT

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

Re: calculate difference between two lists of variables

andreas_lds — Wed, 07 Mar 2018 07:59:50 GMT

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.

Re: calculate difference between two lists of variables

garfield83 — Wed, 07 Mar 2018 13:59:19 GMT

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

Re: calculate difference between two lists of variables

andreas_lds — Wed, 07 Mar 2018 14:18:04 GMT

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;