topic Re: I need a macro for iteratively creating a new variable from a character variable in existing data set in SAS Programming

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

kodmfl — Sat, 05 Jul 2014 18:50:14 GMT

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

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

2 Numeric variable X9

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.

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

Reeza — Sat, 05 Jul 2014 19:12:29 GMT

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.

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

kodmfl — Sat, 05 Jul 2014 19:20:14 GMT

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.

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

Tom — Sat, 05 Jul 2014 23:15:44 GMT

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

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

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

kodmfl — Thu, 17 Jul 2014 16:54:03 GMT

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.

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

data_null__ — Thu, 17 Jul 2014 17:33:17 GMT

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.

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

Tom — Thu, 17 Jul 2014 17:47:32 GMT

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;