DATA Step, Macro, Functions and more

calculate difference between two lists of variables

Reply
New Contributor
Posts: 4

calculate difference between two lists of variables

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!

 

Super Contributor
Posts: 472

Re: calculate difference between two lists of variables

Posted in reply to garfield83

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

 

 

Thanks,
Suryakiran
New Contributor
Posts: 4

Re: calculate difference between two lists of variables

Posted in reply to SuryaKiran

Exactly right

Frequent Contributor
Posts: 109

Re: calculate difference between two lists of variables

Posted in reply to garfield83
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
Super Contributor
Posts: 500

Re: calculate difference between two lists of variables

Posted in reply to garfield83

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.

New Contributor
Posts: 4

Re: calculate difference between two lists of variables

Posted in reply to garfield83

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

Super Contributor
Posts: 500

Re: calculate difference between two lists of variables

Posted in reply to garfield83

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;
Ask a Question
Discussion stats
  • 6 replies
  • 142 views
  • 0 likes
  • 4 in conversation