Proc SQL and Macro language

[hkapoor]

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

[Ron_MacroMaven]

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}

[RW9]

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.