Thank you! ... View more

@twenty7 wrote: I did, however my understanding is that this solution relies on knowing all of the potential values Not knowing all the potential values REALLY REALLY REALLY implies you want a long data set, and then SAS can determine what values exist and act accordingly. But you also said:   @twenty7 wrote: it is needed in this instance as the data is to be exported and shared with non-technical users who need to be able to easily identify which records have a particular code If non-technical users need the wide data set, then get a solution from the long data set, and then "transpose" to wide for these non-technical users.   I see @Ksharp has already done that. ... View more

Thank you!! ... View more

data app; length A1_CITY A2_CITY $20.; input ID $ A1_CITY $ A2_CITY $ NUM_OF_CUSTS; datalines; 001 LONDON LONDON 2 002 LONDON LEEDS 2 003 MANCHESTER LONDON 2 004 LEEDS LONDON 2 ; run; data want; set app; array cities $1 LONDON LEEDS MANCHESTER BIRMINGHAM GLASGOW; array A_CITY A1_CITY A2_CITY; array afford $1. a1_affordability_calc a2_affordability_calc; array increment(5) _TEMPORARY_ (0.25 0.10 0.21 0.23 0.20); array calc $20. a1_calc_result a2_calc_result; do i=1 to dim(cities); cities(i)=byte(96+i); /* a, b, c, ... */ end; do j=1 to dim(A_CITY); do i=1 to dim(cities); if A_CITY(j)=vname(cities(i)) then do; afford(j)=cities(i); leave; end; end; if i le dim(cities) then calc(j)=cats("(x+y+z)+", increment(i)); end; drop i j; run; ... View more

@twenty7 wrote:     I'm trying to find a shorter way of creating a range of macro variables with dates   the code I would usually use is below but I'm finding this is getting longer and longer as I'm now going back 12 months +   DATA _null_; CALL SYMPUT('THIS_MONTH',PUT(INTNX('Month',today(),0,'b'),monyy.)); CALL SYMPUT('MONTH1',PUT(INTNX('Month',today(),-1,'b'),monyy.)); ... CALL SYMPUT('MONTH26',PUT(INTNX('Month',today(),-1,'b'),monyy.)); run; %put &THIS_MONTH; %put &MONTH1; ... %put &MONTH26;     I was hoping that the same would be possible using a do loop... something along the lines of      data _null_; do i=1 to i=5; CALL SYMPUT('MONTH&i',PUT(INTNX('MONTH',today(),-&i.,'b'),monyy.)); end; run; %put &MONTH1; %put &MONTH2;     the error I get in the log is  85 data _null_; 86 do i=1 to i=5; 87 CALL SYMPUT('MONTH&i',PUT(INTNX('MONTH',today(),-&i.,'b'),monyy.)); _ 386 200 WARNING: Apparent symbolic reference I not resolved. ERROR 386-185: Expecting an arithmetic expression. ERROR 200-322: The symbol is not recognized and will be ignored.     Is this to do with the CALL SYMPUT executing before the 1st iteration of the do loop has completed and therefore not knowing the value of &i?   Alternative suggestions would be much appreciated   Thank you!   Several problems. A do loop is do I = 1 to 5; Not do I=1 to I=5;   You do not define a macro variable i. If you paste code and messages from the log the indicator underscore would be under the &i . Data step values aren't referenced with macro &. Second, you are attempting to create a macro variable 'month&i'. Within single quotes the &I would not resolve to values anyway and results in an improper macro variable name. Solution: create a string variable to hold the macro variable name, build the name and then use that in the call symput data _null_; length mvar $ 10; do i=1 to 5; mvar=cats('MONTH',i); CALL SYMPUTx(mvar,PUT(INTNX('MONTH',today(),-i,'b'),monyy.)); end; run; if you are going to create many of these variables make sure the length of mvar is long enough to hold the longest.     Recommend using CALL SYMPUTX to prevent odd blanks appearing at the ends of values. ... View more