Hi all, I have some questions on the below code :
I have 2 time series data sets: data and data_lags, say. The first has say 5 variables (Unemployment, GDP etc) and the second has the same variables lagged. I need a macro ( transf_YoY below) that will create the YoY growth rate for each variable.
The respective variables are in the same order in both data sets(unemp first, GDP next etc).
I have embedded 2 calls to Proc SQL inside the macro that will change the data set variables into macro variables.
Next, I create macro variables indexed by a counter i, corresponsing to each of the variables in data and data_lags.
Finally, inside a Do loop, I perform the YoY transoformation, by taking the difference of the ith variable with its lagged version. In fact, it is in order to perform the transformation inside a loop systematically that I needed to create the indexed vairables in the previous step. I needed to separate the original and the lagged variables into 2 different datasets since the lag and dif functions are not available within a macro function.
My questions are:
Can I use Proc SQL inside a macro as I have done here?
Also, how do I export the transformed YoY variables to a new data set or append it to the original data from inside the macro transf_YoY?
Is the logic below look right?
I don't have immediate access to SAS, and would be thankful for any advice.
%macro transf_YoY( data, data_lags)
## Step 1
## Use Select Into to create macro variables corresponding to data set variables
proc sql noprint;
select Unemp, GDP
into: Unemp, :GDP
from &data;
select lagUnemp, lagGDP
into: lagUnemp, :lagGDP
from &data_lags;
## Step 2
## Create macro variable names indexed by "i" so as to do the YoY transform inside a loop.
## First, do the indexing for the original variables
%let dsid = %sysfunc (open(&data))
%let i = 1;
%do i = 1 %to 5;
%let var&i = %sysfunc(varname(&dsid, &i))
%end;
%let close = %sysfunc(close(&dsid))
## Next, do the indexing for the lagged variables
%let dsid = %sysfunc (open(&data_lags))
%let i = 1;
%do i = 1 %to 5;
%let lagvar&i = %sysfunc(varname(&dsid, &i));
%end;
%let close = %sysfunc(close(&dsid));
## Create YoY transformation using &var_i and & lagvar_i created above
## The final YoY transformed variables are in YoY_var_&i
%do i = 1 %to 5;
%let YoY_var_&i = (&&var&i - &&lagvar&i)/ &&lagvar&i;
%end;
%mend;
Thank you,
Radhika
terminology: macro array is a sequentially-numbered set of macro variables a1--aN
\begin{commentary}
step 1 how many rows in the input data sets?
you are creating only two macro variables from each of your data sets
%put info &=Unemp &=GDP;
%put info &=lagUnemp &=lagGDP;
is that what you want? i.e. does each data set have only one row?
step 2 creating the macro arrays
your stated assumption by the upper-bound of the%do loop is that there are exactly five variables in each data set.
check the n-vars with this statement
%let n_obs = %sysfunc(attrn(&dsid,nobs ));
%let n_vars = %sysfunc(attrn(&dsid,nvars));
then
%do i = 1 %to &n_vars;
you will want separate n_vars for data and lagdata
and these upperbounds have to agree.
step 3 create a macro array YoY*
and what are you doing to do with this macro array?
I think you want to generate a set of data assignment statements
in which case you need a data step; remove the '%let' inside the %do loop
DATA NEW;
set old;
## Create YoY transformation using &var_i and & lagvar_i created above
## The final YoY transformed variables are in YoY_var_&i
%do i = 1 %to 5;
YoY_var_&i = (&&var&i - &&lagvar&i)/ &&lagvar&i;
%end;
\end{commentary}
Sorry, all that &&&&& hurts my eyes. Can you post some sample test data, in a datastep, and what the output should look like. The reason is I can't see why you would need the above code, just use basic datasteps and arrays (or normlise your data). Far easier.
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.