Multiplying columns from two datasets by variable name/position.

[bonanza14]

Hey Guys,

I was wondering if you could help me out with this problem.

So I have two tables named Country_1 and Country_2 and they have variables as in the example below:

data WORK.Country_1;
  infile datalines dlm='~';
  input Date_To:ddmmyy10. Weight_de:8. pct_GDP_DE:8. pct_UR_DE:8.;
  format Date_To:ddmmyy10.;
  datalines;
  31/03/2004~0.4~0.586206896551724~0.37037037037037
30/06/2004~0.4~0.568965517241379~0.314814814814815
30/09/2004~0.4~0.517241379310345~0.259259259259259
31/12/2004~0.4~0.46551724137931~0.185185185185185
31/03/2005~0.4~0.258620689655172~0.12962962962963
30/06/2005~0.4~0.172413793103448~
;;;;

data WORK.Country_2;
  infile datalines dlm='~';
  input Date_To:ddmmyy10. Weight_nl:8. pct_GDP_NL:8. pct_UR_NL:8.;
  format Date_To:ddmmyy10.;
  datalines;
31/03/2004~0.6~0.775862068965517~0.685185185185185
30/06/2004~0.6~0.793103448275862~0.648148148148148
30/09/2004~0.6~0.758620689655172~0.592592592592593
31/12/2004~0.6~0.741379310344828~0.259259259259259
31/03/2005~0.6~0.448275862068966~0.203703703703704
30/06/2005~0.6~~0.166666666666667
;;;;

The problem is to multiply dynamically third column from first DS by its weight and add it to the third column from second DS multiplied by its weight. In this case they are two columns, but I do not know how many columns are in those datasets (can be more). Nevertheless, both DS have the same number of columns. First two are always DATE_TO and Weight and the rest of the columns are positioned by its name, so the multiplication can be done either by their position or part of its name which is common. 

So that would be:

Weight * pct_GDP_DE + Weight * pct_GDP_NL = Col1

Weight * pct_UR_DE + Weight * pct_UR_NL = Col2

I checked two possibilites: arrays and proc iml, but can not really start to do it dynamically.

Thank you for your time and possible solutions,

m

[PeterClemmensen]

So what does your desired result look like given this sample data?

[bonanza14]

data tmp_1;
    merge country:;
    by date_to;
    result_gdp = sum(weight_de * pct_GDP_DE, weight_nl * pct_gdp_nl); 
    result_ur = sum(weight_de * pct_ur_DE, weight_nl * pct_ur_nl); 
    keep date_to result_gdp result_ur;
run;

This is the expected result.

[Kurt_Bremser]

This is the usual consequence of a bad data layout.

Transpose your datasets to long, so you can extract the country and the GDP/UR from _NAME_ and use it for joining.

[bonanza14]

I think that might be the case. I have to play around with that, thank you!