Dynamic Where in SAS - Conditional Operation based on another set -

to disappointment, following code, sums 'value' week 'master' weeks appear in 'transaction' not work -

data master;      input week value;  datalines; 1 10 1 20 1 30 2 40 2 40 2 50 3 15 3 25 3 35 ; run;    data transaction;      input change_week ;  datalines; 1 3  ; run;   data _null_;      set transaction;      until(done);         set master end=done;         week=change_week;           sum = sum(value, sum);     end;      file print;     put week= sum=;  run; 

sas complains, rightly, because doesn't see 'change_week' in master , not know how operate on it.

surely there must way of doing operation on subset of master set (of course, suitably indexed), given transaction dataset... 1 know?

i believe closest answer asker has requested.

this method uses index on week on large dataset, allowing possibility of invalid week values in transaction dataset, , without requiring either dataset sorted in particular order. performance better if master dataset in week order.

for small transaction datasets, should perform quite lot better other solutions retrieves required observations master dataset. if you're dealing > ~30% of records in master dataset in single transaction dataset, quentin's method may perform better due overhead of using index.

data master(index = (week)); input week value; datalines; 1 10 1 20 1 30 2 40 2 40 2 50 3 15 3 25 3 35 ; run;  data transaction; input week ; datalines; 1 3 4 ; run;  data _null_;     set transaction;     file print;      until(done);         set master key = week end=done;         /*prevent implicit retain previous row if key isn't found,           or we've read past last record current key*/         if _iorc_ ne 0 do;           _error_ = 0;           call missing(value);          end;         else sum = sum(value, sum);     end;     put week= sum=; run; 

n.b. work, indexed variable in master dataset must have same name , type variable in transaction dataset. also, index must of non-unique variety in order accommodate multiple rows same key value.

also, possible replace set master... statement equivalent modify master... statement if want apply transactional changes directly, i.e. without sas making massive temp file , replacing original.