topic Re: Proc SQL and Macro language in SAS Programming

Proc SQL and Macro language

hkapoor — Wed, 15 Jul 2015 08:54:59 GMT

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

Re: Proc SQL and Macro language

Ron_MacroMaven — Wed, 15 Jul 2015 14:41:00 GMT

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}

Re: Proc SQL and Macro language

RW9 — Wed, 15 Jul 2015 14:50:23 GMT

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.