/* Richard A. DeVenezia * www.devenezia.com */ /* Original posted in response to * Subject: Re: Assign variables to a new variable based on variable names in SAS * "W M" wrote in message * news:cebd26c2.0401151102.5ab536d4@posting.google.com... */ /* The original problem was posited as * data set having columns a_new n a_1 ... a_20 * and assigning * a_new = value of variable ("a_"||n) <-- not sas code * which is solved simply with * array a_ a_1-a_20; * a_new = a[n]; */ /* The poster further qualified the problem with this supposition * data set having columns a_new n a_1995q1 a_1995q2 ... a_2004q1 * where there might be some 'quarters' not present (and thus not having a column) * and n takes on values such as "1995q2" * This manner of lookup is a special case hash lookup. * The key value is a SAS date value formatted as yyq4 * - presume it to be the first such. * The key value can be functionally mapped to a monotonic increasing * series with zero at some arbitrary baseline date * - allowing the hashing to be implemented as a simple lookup through the mapping * The mapping function is the number of quarters elapsed since the baseline. * * Lookup might be a slight misnomer to some people, in this problem * the lookup is for things 'across' a row, not things 'down' a column. */ /* * Step 1 * * Randomly pick quarters that will have a corresponding column * in some phony data to be created; */ data _null_; qtr = intnx ('qtr', '01jan1995'd, 0); do until (qtr > today()); if ranuni(1) > 0.35 then do; nn+1; call symput ('N' || put(nn,2.-L), put (qtr, yyq6.)); end; qtr = intnx ('qtr', qtr, 1); end; call symput ('NN', put(NN,2.)); run; /* * Step2 * * Create some phony data */ %macro makeFaux; %local i j; data faux (drop=j); do row = 1 to 1000; length n $6; length a_new 8; retain a_new .; * assign values to the a_* columns; %do i = 1 %to &NN; a_&&N&i = int (ranuni(2)*1000); %end; * n corresponds to one of the a_{value-of-n} columns; j = 1 + int(ranuni(0) * &NN); n = symget ('N' || put(j,2.-L)); output; end; run; %mend; %makeFaux; /* * Step 3 * * Perform assignment a_new = a_{value-of-n} assignment */ %let maxIndex = 1000; data anew; set faux; array aVars a_: ; length varname $32; retain aBase; array aMap [&maxIndex] _temporary_; if _n_ = 1 then do; * determine intermediate mapping array; * tells us which qtr belongs to which a_* variable; aBase = intnx ('qtr', '01jan1960'd, 0); do _i_ = lbound(aVars) to hbound(aVars); call vname (aVars(_i_), varname); qtr = input (substr(varname,3),??yyq6.); if qtr ne . then do; qtrIndex = intck ('qtr', aBase, qtr); if not (1 <= qtrIndex <= &maxIndex) then do; put "ERROR: Out of bounds, " qtrIndex=; stop; end; aMap [qtrIndex] = _i_; end; end; end; qtr = input (n,??yyq6.); if qtr then do; qtrIndex = intck ('qtr', aBase, qtr); if not (1 <= qtrIndex <= &maxIndex) then do; put "ERROR: Out of bounds, " qtrIndex=; stop; end; if aMap[qtrIndex] = . then do; put "ERROR: Out of bounds, " n=; stop; end; a_new = aVars[aMap[qtrIndex]]; end; run;