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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.