SAS data step - dynamic select field from table

Question

I have little problem with one case. Please look at table HAVE. And WANT. My process: get value from VAR, divide by 12, round up to the nearest whole number and put in WANT table, in RES field value from FIELD&"result of the action". How I can do that? My final table has thousand records, and these fields hundred - by two kind.

DATA HAVE;
infile DATALINES dsd missover;
input ID VAR FIELD1 FIELD2 FIELD3 FIELD4;
CARDS;
1, 10, 1, 6, 5, 6
2, 20, 1, 6, 8, 8
3, 30, 5, 8, 12, 7
4, 40, 5, 9, 5, 6
5, 50, 8, 10, 12, 8
;run;

DATA WANT;
infile DATALINES dsd missover;
input ID RES;
CARDS;
1, 1
2, 6
3, 12
4, 6
5, 8
;run;

THANK YOU!!

score 2 · Accepted Answer · answered Mar 26 '20 at 15:39

2

Looks like you want to use an ARRAY statement. This will allow you reference a variable by an index into the array.

data want;
  set have ;
  array f field1-field4;
  index=ciel(var/12);
  if 1 <= index <= dim(f) then res=f[index];
run;

answered Mar 26 '20 at 15:39

Tom

47,574
2
16
29

score 2 · Answer 2 · answered Mar 26 '20 at 15:50

Arrayify your variables and compute the index appropriately. You can work with multiple arrays of variables if you have hundreds of fields similarly present.

data want;
  set have;
  array costs cost_year1-cost_year4;
  array plans plan_year1-plan_year4;

  res_costs = costs[ceil(var/12)];
  res_plans = plans[ceil(var/12)];
run;

I would not check the index computation and let the step fail if the var value is out of range (<0 or > 40). The ERROR: will tell you the data values do not meet the expected data model and some correction in policy or data collection needs to occur.

Richard, Tom - big thank you for beautiful solutions! – DarkousPl Mar 27 '20 at 06:19 — DarkousPl, Mar 27 '20 at 06:19

SAS data step - dynamic select field from table

2 Answers2