CODEWITHSUNDEEP
sas
Samuel
Samuel

Reputation: 13

use field value as a variable in sas

Wonder if you can help me

I’ve got a dataset where the value in a column is also the field name of a column. I want to be able to use the value of the column to call the applicable field in a formula.

For instance … I have columns…

MERCH_NO
V01
M02
V08
M08
AMOUNT
PLAN

A record would look like this …and what I want the calc field to do…

MERCH_NO      V01   M02  V08  M08  AMOUNT    PLAN    CALC
123456        2     2    1    1    100.00    V01     value of V01 * AMOUNT
456789        4     4    4    4    250.00    M08     value of M08 * AMOUNT

If the PLAN field for a record says V01, then the value of the V01 column must be used in the CALC field. If the PLAN field says, M08, then the M08 value should be used. There are about 40 plans.

Upvotes: 1

Views: 1270

Answers (2)

Joe
Joe

Reputation: 63424

Vasja's approach is the correct one - here is that approach using the PLAN variable as you describe.

data have;
input MERCH_NO      V01   M02  V08  M08  AMOUNT    PLAN $;
calc = input(vvaluex(plan),best12.) * amount;
put calc=;
datalines;
123456        2     2    1    1    100.00    V01     
456789        4     4    4    4    250.00    M08    
;;;;
run;

Upvotes: 0

vasja
vasja

Reputation: 4792

A static example of how to use VVALUEX() function for that.

data result;
V01 = 2;
AMOUNT=100;
CALC = 'value of V01 * AMOUNT';
length arg1 arg2 $32;
arg1 = scan(compress(CALC, 'value of'), 1);
arg2 = scan(compress(CALC, 'value of'), 2);
put arg1 arg2;
result = input(VVALUEX(arg1), 16.) * input(VVALUEX(arg2), 16.);
run;

For your situation, you'd have to create logic to recognize all know patters of CALC, types and formats of variables (since VVALUEX() returns formatted values).

A dynamic approach but probably not suitable for lots of data is to generate the code for each row (see below). Currently assumes a simple expression usable in IF .. THEN.

data input;
length CALC  $50;
input V01 M08 AMOUNT  CALC 9-58;
cards;
2 1 100 value of V01 * AMOUNT
2 4 100 value of M08 * AMOUNT
;
run;

/* code generation */
data _null_;
file 'mycalc.sas';
set input end=last;

length line $150;
if _N_=1 then do;
    put 'data result;';
    put '   set input;';
end;
line = 'if _N_ = ' || put(_N_, 8. -L) || 
         ' then RESULT = ' || compress(CALC, 'value of') || ';';
put line;
if last then put 'run;';
run;

%include 'mycalc.sas'; /* run the code */

Ok, now if see I didn't notice your note about PLAN field - please adjust as you need.

Upvotes: 1

Related Questions