DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 40

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

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=&variableSmiley Happy;

run;

Super User
Posts: 10,550

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

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

Super Contributor
Posts: 333

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

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.

EJ

Contributor
Posts: 40

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

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)

Super Contributor
Posts: 339

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

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

Contributor
Posts: 40

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

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.

86        

87         GOPTIONS NOACCESSIBLE;

88         %LET _CLIENTTASKLABEL=;

89         %LET _CLIENTPROJECTPATH=;

90         %LET _CLIENTPROJECTNAME=;

91         %LET _SASPROGRAMFILE=;

92        

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

94         ODS _ALL_ CLOSE;

95        

96        

97         QUIT; RUN;

Many Thanks

Super Contributor
Posts: 339

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

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

Contributor
Posts: 40

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

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 !]

Contributor
Posts: 40

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

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

Super Contributor
Posts: 339

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

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

Contributor
Posts: 40

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

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

Super Contributor
Posts: 339

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

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

Super Contributor
Posts: 339

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

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

     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;

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

Ask a Question
Discussion stats
  • 12 replies
  • 337 views
  • 0 likes
  • 4 in conversation