I need a macro for iteratively creating a new variable from a character variable in existing data set

[kodmfl]

I have a data set containing the following:

Several character variables with location information say just a numeric value for LOCATION from 1 to the total number, a response variable (a Y variable), several numeric predictor variables (X variables X1 to however many say 20) and a character variable (call it BESTMODEL) that indicates which of these variables has the best relationship with the response variable.  I want to create a new variable(call it BESTVAR) which for each iteration of RIVER, REGION, STATION, contains the numeric values of the predictor which has the best relationship with Y , There will be multiple observations per location but the character variable is constant for a given location. I presume that the only way to do this is to create individual data sets for each location create the BESTVAR for each data set and then recombine them into a single data set containing all the locations. 

Data would look like this

Location   Yvariable          X1   X2   .....XMAXIMUM     BESTMODEL (Character)

1               Numeric         Numeric variables                X2

1               .                    .                                         X2

1               .                    .                                         x2

1               .                     .                                        x2

2               .                    .                                         x9

2               .                    .                                         x9

2               .                    .                                         x9

2               .                    .                                         x9

3               .                    .                                         x1

3               .                                                              x1

3              .                     .                                         x1

3              Numeric          Numveric variables .             x1

What I want is

Location   Yvariable            BESTVAR

1              Numeric            Numeric variable X2

1                                      Numeric variable X2

1                                      Numeric variable X2

1                                      Numeric variable X2

2                                      Numeric variable X9

2                                       Numeric variable X9

2                                       Numeric variable X9

2                                       Numeric variable X9

3                                        Numeric variable X1

3                                        Numeric variable X1

3             Numeric               Numeric variable X1

So in others words I want the numeric values in the columns identified in BESTMODEL to be substituted into the variable BESTVAR.  I don't want to have a call for each location or each variable. have a numeric counter in the data set for the location and a global macro variable for the total number of locations.  It would seem that this should be a fairly straight forward thing to do but I simply can't seem to find the right tool I have tried several things to get this to work SYMPUT and CALL EXECUTE. I think the main problem I am having is trying to get the macro DO loop to iterate in the proper way. I can't seem get anything to work properly.

If anyone has a suggestions, references or example code it would be most appreciated.

[Reeza]

I don't understand your question, if you already have BESTMODEL (character) what more do you need? You talk about 3 different location variables, river/ region/station but they aren't shown in your data.

Better examples would be helpful.

[kodmfl]

Well for my example I didn't actually want to type out all of the different values for the locations so I just put in an indicator for let's say just STATION. As for why I want to move the data into another column their are very good reasons for it. Believe me when I say it will help me streamline things in a larger program.

[Tom · Accepted solution]

Doesn't sound you need any macro code for that. Just use VVALUEX() function.

newvar = input( vvaluex( bestmodel ), ??32. );

[kodmfl]

Tom

You wouldn't believe how long I banged my head on my desk over this. Thanks for your help. Hope you get this message, I've never heard of this function obviously until ,,,well...now.

[data_null__]

Isn't there a potential data loss issue depending on the formatted value of argument to VVALUEX.  Could you use HEX16 format/informat to guard against that?

However it may not be an issue of any consequence. 

[Tom]

Yes. 

Another way is to  use WHICHC() function to figure out which index to use into an array of the values.

* Some sample data ;

data have ;

  input location y x1-x5 bestmodel $ ;

  cards;

1 3.4 1 2 3 4 5 X2

2 1.3 6 7 8 9 10 X4

run;

* Get variable names ;

proc transpose data=have(obs=0) out=vars;

  var x: ;

run;

proc sql noprint ;

  select quote(trim(upcase(_name_))) into :varlist separated by ','

  from vars ;

quit;

%let nvars=&sqlobs;

* Lookup the BESTMODEL value ;

data want ;

  set have ;

  array x (&nvars) ;

  array names (&nvars) $32 _temporary_ (&varlist) ;

  newvar = x(whichc(upcase(bestmodel),of names(*)));

  put bestmodel= newvar=;

run;