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!
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
Exactly right
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.
Right, but my question is how to do that using macro? I have 600 variables.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.