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 ... View more

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 ... View more

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 ... View more

Based on your desired objectives for your macro, you will definitely significantly lessen the syntax difficulties using the automatic variable &SQLOBS for your countsas well as the range-macro variables as Tom pointed out. As an attempt to provide a clearer explanation why the unquote/nrstr combination solves the very issue that you had, I will suggest a few things. 1. Whenever errors or warnings are issued for "macro variable blah resolves or cannot be resolved", use option symbolgen; and read the details of the segment around your warnings/errors. It basically provides each step that the macro processor does to execute the macro elements of any given line (at compilation phase). 2. Always keep in mind that the core concept behind the macro processor is that of a text parser. It basically allows you to parse a bunch of SAS code that you would just not want to hard type. The difficulty underlying with this, is that anytime you need to extract data (through data step or proc sql in general) via execution phase statements to store it in macro variables for use within the same macro. It can create a giant turmoil of timeliness (the macro variables you thought had been created will only be "later" when the code segment gets executed so when you attempt to dereference them, they don't exist) 3. Macro variables dereferencing happens naturally before macro functions (This is the main issue that caused your error). Thus, if you ought to do tricky task where information about a macro variable that you wish to deref is derived after applying an inline %function, you will need to manipulate the macro processor to delay the outer ampersands until after the %functions are resolved. Let's go back to the example by Jakkas %Let colCount = &&countCol%Trim(%Left(&count)); In this scenario, since ampersands are resolved before the %functions, the macro processor does this (which can be validated with options symbolgen): 1st pass && resolves to &, &count resolves to            5 2nd pass &countCol cannot be resolved, issueing the error 3rd pass (inner functions first) %left(          5) is resolved to 5 (symbolgen will use TEXT automatic macro var for most text-oriented in-line macro functions) 4th pass (outer function) %trim(5) is resolved to 5 However, %nrstr() prevents any macro elements inside the paranthesis to be resolved by the macro processor unless controlled otherwise. Thus %Let colCount = %unquote(%nrstr(&countCol)%Trim(%Left(&count.))); is resolved as follow first pass skip over &countCol because it is %nrstr(), resolve &count as          5 2nd pass (resolving %inline function from inside to outside) %left(          5) resolves as 5 3rd pass %trim(5) resolves as 5 4th pass %unquote(%nrstr(&countCol)5) resolves as &countCol5 5th pass &countCol5 resolves as 10000 in practice %nrstr(&countCol) actually does get resolved and if you read the documentation, SAS simply changes the & representation to it's unicode?(maybe it's ascii or hex or even binary representation - regardless, not one that is read as an & by the macro processor). For simplicity, simply consider that it only gets resolved once it is unquoted by %unquote. Hope this explanation helps you a little. P.S. %STR, %NRSTR, %QUOTE, %NRQUOTE and %UNQUOTE eventually are necessary but very, very often. The code can be simplified elsewhere to avoid having to twist your bring to figure out when what should be resolved and have a better code flow/readability. Hence, the tips pointed above, namely the generic one to remove leading blanks and the various proc sql interractions with the macro facility pointed out by Tom are much better alternatives. ... View more

Going from Jakkas' example, the issue is that %trim and %left functions are causing issue because they are normally resolved after the ampersand dereferencing (which only makes sense since you ought to have the inner macro variables derefed before the functions are applied) As such, here is an alternative to fix the dereferencing order options symbolgen; data temp;      Do i =1 To 5; output; end; Run; Proc Sql;      Select Count(*) into :Count from temp; Quit; %Let countcol5 = 10000; %Let colCount = %unquote(%nrstr(&countCol)%Trim(%Left(&count.))); %Put &colcount; Since % macro functions resolving goes from inside out, it goes &count. %left %trim (the %nrstr(&countcol) prevents the & to be read as a macro syntax element but rather just as raw text just yet) %unquote allows to deref the &countcol that was quoted after the %trim/%left have already been resolved ... View more

There is a lot of things that I am questionning myself about in your syntax logic making it difficult to seize exactly where the issue is. My first suggestion is to change use an intermediate step to do the trim/left combo on count only once so that all further deref of &count. needs not to be formated. Based on the Error log, it appears as though %TRIM(%LEFT()) does not get resolved within the double quotes of statement %let colcount= "&&countCol%TRIM(%LEFT(&count))"; It appears like a str/quote/unquote job has to be worked around. However, I don't quite understand I suggest you provide your desired objectives for this macro so that we can help you polish the idea a little. The count of column in each dataset segment I can't understand what is your desired intention with the 3 statements preceeding the %end; Also, interractions between macros and raw code (in your case proc SQL; ) for each raw code segment that produces macro variable can be very tricky. Since macro statements are normally ran at compilation phase whereas raw SAS code statements are only ran at execution phase, it is quite possible that, because they are embeded within a %do ...; %end; macro loop block, that each       proc sql noprint;       select count(*) into :countCol%TRIM(%LEFT(&count))             From &lib..%upcase(&&dsname&k)       ;       quit; only get executed after the entire %do/%end block are executed. Thus, at compilation phase of this segment of the macro, :countCol&tablecount. has not been assigned a value because the proc sql was not yet ran (it only is after the entire do/end block has been parsed as code for execution). That could be another reason why you get this bug. As an example of a way around (and to test if it is that which is causing your bug) I suggest trying the following change from %do k=1 %to &tablecount;       proc sql noprint;       select count(*) into :countCol%TRIM(%LEFT(&count))             From &lib..%upcase(&&dsname&k)       ;       quit;       /* here is where the error crops up */             %let colcount= "&&countCol%TRIM(%LEFT(&count))";       %let collen = %length(&&colcount);       %put(" length is &collen"); %end; to %do k=1 %to &tablecount;       proc sql noprint;       select count(*) into :countCol&k.             From &lib..%upcase(&&dsname&k)       ;       quit; %end; %do k=1 %to &tablecount;       %let colcount= "&&countCol&k..";      %put colcount=&colcount.;       %let collen = %length(&&colcount);       %put(" length is &collen"); %end; This should help you give some insight on what was wrong in that segment ... View more

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 ... View more

It is also possible, since your X1 appears to be continuous, that all extreme values of X1 have Y=1 whereas the 32% below the median is distributed moreso randomly accross the n/2 cases. Maybe I need to review my log regression litterature but it appears to me as though if all your Y=1 above the median occur with your highest values of X1 with a few extreme values, that it would be reasonable, for the log odds of a shift of 1 unit in X1 to have a positive effect in a model. If you output the proc logistic data (or maybe that is only availible with proc genmod) but there is an "influence" statistics. Try a model with only X1, sort the resulting dataset by X1 and take a look at the influence statistics column for the records with high values of X1 ... View more