Create a dynamic logic based on data available

[zqkal]

Hi all,
I have situation where i need to constantly update my code each time the data changes ( added or removed). I'm thinking to build a dynamic logic that get contracted based on data available.

Here is an example: I have a dataset similar the one below. Here is what i want. 

1. order the ratio column alphabetically [A-Z]. in this case the order will be [ratio_c1, ratio_d1, ratio_d2]

2. using the order calculate a new column using the below logic. 

new_ratio_c1 = (balance * pbal)* ratio_c1

new_ratio_d1 = (balance * pbal)* ratio_d1 + pbal * 1 - ratio_c1 - ratio_d1

new_ratio_d2 = (balance * pbal) * ratio_d2 + pbal * 1 - ratio_c1 - ratio_d1 - ratio_d2 

The logic need to update itself if there are a new ratio. for example, there was ratio_d3 

new_ratio_d3 = (balance * pbal) * ratio_d3 + pbal * 1 - ratio_c1 - ratio_d1 - ratio_d2 - ratio_d3

data have; 
input group $ month balance pbal ratio_d2 ratio_d1 ratio_c1;
datalines; 
A 0 2050 100 .002 .051 .001
A 1 2030 100 .002 .021 .092
A 2 2010 100 .004 .022 .001
B 0 1500 400 .002 .042 .091
B 1 1450 400 .012 .132 .061
B 2 1430 400 .015 .232 .051
B 3 1420 400 .129 .023 .051
C 0 2300 300 .165 .002 .021
C 1 2350 300 .098 .026 .041
;
run;

I really appreciate your kind help and suggestions. 

Thanks in advance 

[Astounding]

Here's a starting point:

proc sql;
select distinct name into : varlist
from dictionary.columns where
memname = 'HAVE' and
libname = 'WORK'
and upcase(name) =: 'RATIO_';
quit;

You probably need to check the syntax on these tools (is UPCASE needed in other places, will =: work  in SQL).  But the trick is that SELECT DISTINCT will automatically alphabetize values in the list.

From there, the rest is not too bad:

%macro final;

data want;
   set have;
   array vars {&sqlobs} &varlist;
.......

%mend final;

I don't have time to work on the rest of it, but it should be straightforward as you run through the list of words within &VARLIST in a macro loop.

[Patrick]

@zqkal 

Does below return what you're after?

data have; 
input group $ month balance pbal ratio_d2 ratio_d1 ratio_c1;
datalines; 
A 0 2050 100 .002 .051 .001
A 1 2030 100 .002 .021 .092
A 2 2010 100 .004 .022 .001
B 0 1500 400 .002 .042 .091
B 1 1450 400 .012 .132 .061
B 2 1430 400 .015 .232 .051
B 3 1420 400 .129 .023 .051
C 0 2300 300 .165 .002 .021
C 1 2350 300 .098 .026 .041
;
run;

%let source_vars=;
%let target_vars=;
proc sql noprint;
  select 
    name,
    cats('new_',name)
      into 
        :source_vars separated by ' ',
        :target_vars separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='HAVE'
  and upcase(name) like 'RATIO^_%' escape '^'
  order by upcase(name)
  ;
quit;

data want(drop=_:);
  set have;
  array source_var {*} &source_vars;
  array target_var {*} &target_vars;

  target_var[1]=balance * pbal * source_var[1];
  _sum01=source_var[1];
  do _i=2 to dim(target_var);
    _sum01=_sum01+source_var[_i];
    target_var[_i]=balance * pbal * source_var[_i] + pbal - _sum01;
  end;

run;

[Tom]

What is it about your process that you don't know what the data structure is that you are receiving?

Can't you just assume that the ratio variables will appear in the right order in the incoming dataset so that you can just use a variable list?

array ratio ratio_:;

Why would they appear out of order?  

[Kurt_Bremser]

Transpose the ratio_: variables to a long dataset, sort it, and apply your logic to the sequence. Then the code is completely data-driven.

See Maxim 33.