topic Re: How to transform that into a macro (where one can choose what he want to see) ? in SAS Programming

How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Tue, 10 Sep 2013 13:53:49 GMT

This is the last episode of my fantastic saga (ahah)

Now, I've got a period from 2007Q2 to 2013Q2, so 24 quarters

I have 24 tables (one per quarter) with the column ID (clients) and some quantitative variables (let say quantities, people etc.)

Note that the column ID is the same for the 24 tables

What I'd like to do is this (see my code below) but inside a macro, knowing that the period is determined via prompts that user chooses (YearStart YearEnd QuarterStart QuarterEnd). So what I did below is clearly not good as I have to manually write the quarters etc.

Besides, a prompt is also used here (variable) with which the user chooses what variable he wants to see in the final table (Rating or EAD).

Thanks a lot for your help

PROC SQL;

CREATE TABLE CREDITR.HISTO_RATINGS2 AS

SELECT t1.Client,

t1.CD_COTATION AS RATING_Q3_07,

t2.CD_COTATION AS RATING_Q4_07,

t3.CD_COTATION AS RATING_Q1_08,

t4.CD_COTATION AS RATING_Q2_08,

t5.CD_COTATION AS RATING_Q3_08,

t6.CD_COTATION AS RATING_Q4_08,

t7.CD_COTATION AS RATING_Q1_09,

t8.CD_COTATION AS RATING_Q2_09,

t9.CD_COTATION AS RATING_Q3_09,

t10.CD_COTATION AS RATING_Q4_09,

t11.CD_COTATION AS RATING_Q1_10,

t12.CD_COTATION AS RATING_Q2_10,

t13.CD_COTATION AS RATING_Q3_10,

t14.CD_COTATION AS RATING_Q4_10,

t15.CD_COTATION AS RATING_Q1_11,

t16.CD_COTATION AS RATING_Q2_11,

t17.CD_COTATION AS RATING_Q3_11,

t18.CD_COTATION AS RATING_Q4_11,

t19.CD_COTATION AS RATING_Q1_12,

t20.CD_COTATION AS RATING_Q2_12,

t21.CD_COTATION AS RATING_Q3_12,

t22.CD_COTATION AS RATING_Q4_12,

t23.CD_COTATION AS RATING_Q1_13,

t24.CD_COTATION AS RATING_Q2_13,

t1.EAD AS EAD_Q3_07,

t2.EAD AS EAD_Q4_07,

t3.EAD AS EAD_Q1_08,

t4.EAD AS EAD_Q2_08,

t5.EAD AS EAD_Q3_08,

t6.EAD AS EAD_Q4_08,

t7.EAD AS EAD_Q1_09,

t8.EAD AS EAD_Q2_09,

t9.EAD AS EAD_Q3_09,

t10.EAD AS EAD_Q4_09,

t11.EAD AS EAD_Q1_10,

t12.EAD AS EAD_Q2_10,

t13.EAD AS EAD_Q3_10,

t14.EAD AS EAD_Q4_10,

t15.EAD AS EAD_Q1_11,

t16.EAD AS EAD_Q2_11,

t17.EAD AS EAD_Q3_11,

t18.EAD AS EAD_Q4_11,

t19.EAD AS EAD_Q1_12,

t20.EAD AS EAD_Q2_12,

t21.EAD AS EAD_Q3_12,

t22.EAD AS EAD_Q4_12,

t23.EAD AS EAD_Q1_13,

t24.EAD AS EAD_Q2_13

FROM CREDITR.RATINGS1 t1, CREDITR.RATINGS2 t2, CREDITR.RATINGS3 t3, CREDITR.RATINGS4 t4, CREDITR.RATINGS5 t5,

CREDITR.RATINGS6 t6, CREDITR.RATINGS7 t7, CREDITR.RATINGS8 t8, CREDITR.RATINGS9 t9, CREDITR.RATINGS10 t10,

CREDITR.RATINGS11 t11, CREDITR.RATINGS12 t12, CREDITR.RATINGS13 t13, CREDITR.RATINGS14 t14, CREDITR.RATINGS15

t15, CREDITR.RATINGS16 t16, CREDITR.RATINGS17 t17, CREDITR.RATINGS18 t18, CREDITR.RATINGS19 t19,

CREDITR.RATINGS20 t20, CREDITR.RATINGS21 t21, CREDITR.RATINGS22 t22, CREDITR.RATINGS23 t23, CREDITR.RATINGS24

t24

WHERE (t1.Client = t2.Client AND t1.Client = t3.Client AND t1.Client = t4.Client AND t1.Client = t5.Client AND

t1.Client = t6.Client AND t1.Client = t7.Client AND t1.Client = t8.Client AND t1.Client = t9.Client AND

t1.Client = t10.Client AND t1.Client = t11.Client AND t1.Client = t12.Client AND t1.Client = t13.Client AND

t1.Client = t14.Client AND t1.Client = t15.Client AND t1.Client = t16.Client AND t1.Client = t17.Client AND

t1.Client = t18.Client AND t1.Client = t19.Client AND t1.Client = t20.Client AND t1.Client = t21.Client AND

t1.Client = t22.Client AND t1.Client = t23.Client AND t1.Client = t24.Client);

QUIT;

DATA CREDITR.HISTO_RATINGS2 ; SET CREDITR.HISTO_RATINGS2 (keep=&variable:);

run;

Re: How to transform that into a macro (where one can choose what he want to see) ?

ballardw — Tue, 10 Sep 2013 14:00:08 GMT

What environment do you want this in? Enterprise Guide, Base SAS, AF / FSP or something else?

Re: How to transform that into a macro (where one can choose what he want to see) ?

esjackso — Tue, 10 Sep 2013 14:12:15 GMT

I probably dont understand the end use case well enough but based on the information in the post it seems like making the data wider will make the problem harder. Appending the data together and specifying a new date variable based on the dataset the information is coming from.

This could reduce the number of date prompts as well to just start YR Q and end YR Q.

Like I said if Im off base on the use case this may not make sense .... just throwing it out there.

Re: How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Tue, 10 Sep 2013 14:48:44 GMT

Hello and thanks guys

I'm doing this on EG

Yeah I understand that getting all the variables in first place is very not optimal. Then my problem would be to specify which variable to collect (i.e. here from 24 quarters) in one table.

Besides, I also need it to be automatised as the user can choose the length of the period (about starting and ending quarter)

Re: How to transform that into a macro (where one can choose what he want to see) ?

Vince28_Statcan — Tue, 10 Sep 2013 16:53:55 GMT

Hi GuiVtzl,

This can be done in many ways depending on what parameters your users wish to enter. E.g. either only a number of quarters backtracking from today (one parameter), a start date and number of quarters (2 params) a start date and end date (2 param) etc. etc.

As one example of how you could macro this with 4 parameters: start date/end date and quarters since that's what you've mentioned up top (I would've opted for a YYQ4 syntax 2 parameter input personally as it felt more natural). For simplicity at the moment, this assumes that no year before 2000 might be queried. If so, I would recommend using 4 digit year inputs instead to not cause a bunch of special handling for substractions

/* user must enter start date and end dates using the YYQ4 syntax e.g. 07Q3 13Q2 */

%macro histo_rating(startY=, startQ=, endY=, endQ=, desiredvar=);

%let qspan = %eval( (&endy.-&starty.-1)*4) + (4-&startQ.+1) + &endq.);

%do i=1 %to &qspan.; /*create indexed macro variables to simplify looping */

%let Q&i. = %sysfunc(putn(%sysfunc(mod(&startQ.+&i.-1, 4)), z1.));

%let Y&i. = %sysfunc(putn(%eval(&startY.+%sysfunc(floor(((&startQ.+&i.-1)-1)/4))), z2.));

%end;

proc sql;

create table creditr.histo_ratings_&startY.Q&startQ._to_&endY.Q&endQ. as

select t1.client

%do i=1 %to &qspan.;

, t&i..&desiredvar. as &desiredvar._Q&&Q&i.._&&Y&i.. /* you may need to review period macro variable delimiters */

%end;

from

CREDITR.RATINGS1 as t1 /* Initiating before looping to balance comma delimiters - it is obviously not logic for your tables to be indexed 1-24 unless they were created within the macro following the same quarters patterns it seems so you will have to review that once you get the hang of the macro */

%do i=2 %to &qspan.;

, CREDITR.RATINGS&i. as t&i.

%end;

WHERE

t1.CLIENT=t2.CLIENT /* Initiating before looping to balance AND */

%do i=3 %to &qspan.;

and t1.CLIENT = t&i..CLIENT

%end;

; /* closing SQL query */

quit; /* Closing proc sql */

%mend;

%histo_rating(startY=07, startQ=3, endY=13, endQ=2, desiredvar=CD_COTATION);

This is all untested but shouldn't be too far off the appropriate syntax. If you need further help debugging, please provide a small sample of 3 tables each with 2-3 client records through datalines statement or something similar or minimally provide the log error report.

If you want to allow for user to enter both EAD and CD_COTATION or want the user to enter RATINGS instead of CD_COTATION, it can be done fairly simply in a similar fashion to that of the preformating new macro variables used

Hope this helps!

Vincent

Re: How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Wed, 11 Sep 2013 10:00:43 GMT

Thanks a lot Vincent, this is exactly what I was looking for.

Now I would like to ask for help debugging please...

When I run the macro, I got this error message (I used your exact code):

Oh I have another question : How do I adapt the name of the FROM tables Ratings ? In my project they are named RATINGSQ3_2007 and so on.

So, the log I got is ;

%histo_rating(startY=07, startQ=3, endY=13, endQ=2, desiredvar=CD_COTATION);

ERROR: Required operator not found in expression: &qspan.

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro HISTO_RATING will stop executing.

87 GOPTIONS NOACCESSIBLE;

88 %LET _CLIENTTASKLABEL=;

89 %LET _CLIENTPROJECTPATH=;

90 %LET _CLIENTPROJECTNAME=;

91 %LET _SASPROGRAMFILE=;

93 ;*';*";*/;quit;run;

94 ODS _ALL_ CLOSE;

97 QUIT; RUN;

Many Thanks

Re: How to transform that into a macro (where one can choose what he want to see) ?

Vince28_Statcan — Wed, 11 Sep 2013 11:39:27 GMT

Hi GuiVtzl,

I had an unbalanced set of paranthesis for

%let qspan = %eval( (&endy.-&starty.-1)*4) + (4-&startQ.+1) + &endq.);

Simply remove the bolded paranthesis above (or replace the line with)

%let qspan = %eval( (&endy.-&starty.-1)*4 + (4-&startQ.+1) + &endq.);

Using the following you can play with the paranthesis and figure out why it was causing such a big turmoil; the shorter portion was evaluated but the rest would be kept as string thus when &sqpan would be derefed elsewhere, it wouldn't be a valid number for loop boundariers

options symbolgen;

%macro histo_rating(startY=, startQ=, endY=, endQ=, desiredvar=);

%let qspan = %eval( (&endy.-&starty.-1)*4) + (4-&startQ.+1) + &endq.);

%put qspan=&qspan.;

%mend;

%histo_rating(startY=07, startQ=3, endY=13, endQ=2, desiredvar=CD_COTATION);

Ask again if anything else goes wrong.

Vince

Re: How to transform that into a macro (where one can choose what he want to see) ?

Vince28_Statcan — Wed, 11 Sep 2013 11:47:58 GMT

As to answer how to change the FROM statement according to your tables naming convention of RATINGSQ3_2007 and so on,

The FROM block

from

%do i=2 %to &qspan.;

, CREDITR.RATINGS&i. as t&i.

%end;

Should be changed as follow:

from

CREDITR.RATINGSQ&Q1._20&Y1. as t1 /* Initiating before looping to balance comma delimiters - it is obviously not logic for your tables to be indexed 1-24 unless they were created within the macro following the same quarters patterns it seems so you will have to review that once you get the hang of the macro */

%do i=2 %to &qspan.;

, CREDITR.RATINGSQ&&Q&i.._20&&Y&i.. as t&i. /* Note again, since you are bouncing between 2 digit and 4 digit year naming convention, the current macro doesn't support 1900s */

%end;

Vincent

Re: How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Wed, 11 Sep 2013 11:54:30 GMT

Thanks Vincent, it works well !

But since my original RATINGS tables are named RATINGSQ3_2007 RATINGSQ4_2007, how could I adapt the code in the "FROM" part ?

[SORRY MY COMPUTER LAGGED SO I DIDN'T SEE YOUR SECOND POST !]

Re: How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Wed, 11 Sep 2013 12:00:21 GMT

It works fine again, but I think there's a little problem elsewhere.

In fact there's a little problem as regard the quarters numbers. Indeed it goes 1 2 3 0 instead of 1 2 3 4

Re: How to transform that into a macro (where one can choose what he want to see) ?

Vince28_Statcan — Wed, 11 Sep 2013 12:09:42 GMT

Indeed, I used modulo 4 to build it and did not adjust -_- I'm sorry about this let me see it's probably a simple case of changing paranthesis ordering so that a +1 is applied after the modulo

Let's shift the translation around to get 0 1 2 3 from modulo (instead of 1 2 3 0) and then add +1

%let Q&i. = %sysfunc(putn(%eval(%sysfunc(mod(&startQ.+&i.-2, 4))+1), z1.));

I've bolded the changes

Looks like it should correct the issue...

here's my test setup

%macro histo_rating(startY=, startQ=, endY=, endQ=, desiredvar=);
%let qspan = %eval( (&endy.-&starty.-1)*4 + (4-&startQ.+1) + &endq.);
%do i=1 %to &qspan.; /*create indexed macro variables to simplify looping */
%let Q&i. = %sysfunc(putn(%eval(%sysfunc(mod(&startQ.+&i.-2, 4))+1), z1.));
%let Y&i. = %sysfunc(putn(%eval(&startY.+%sysfunc(floor(((&startQ.+&i.-1)-1)/4))), z2.));
%end;
%put Q1=&Q1. Q2=&Q2. Q3=&Q3. Q4=&Q4.;
%mend;

%histo_rating(startY=07, startQ=3, endY=13, endQ=2, desiredvar=CD_COTATION);

I tested with %put _user_; at first and the Y&i variables looked properly built so we should almost be done with logic debugging

Vince

Re: How to transform that into a macro (where one can choose what he want to see) ?

GuiVtzl — Wed, 11 Sep 2013 12:13:51 GMT

Vincent,

How to say it ? Many many thanks.

You just saved my life at work (I am a complete beginner with SAS and my boss gave me bad "manual" code to automatised).

Thanks again, I carefully wrote down your code and I'm going to analyse it to get better with SAS

Re: How to transform that into a macro (where one can choose what he want to see) ?

Vince28_Statcan — Wed, 11 Sep 2013 12:20:27 GMT

Yw, I actually enjoy tinkering around macros so it was not work for me anyway.

If you ever need further help to understand how/what happens through the macro, feel free to come back to this thread and ask for further explanation. It might not be as almost real time as it was this morning since I don't work for SAS but I typically check communities as my morning routine unless I have urgent project deadlines.

Vince