transposing data

[Oleg_L]

Dear All!

I need  help. I do not know how to find the solution.

I have table1 and table2 and I need to transpose table1 and table2 to table3  

data table1;

infile cards;

informat account $3. date ddmmyy10. sum 8.;

input account--sum;

cards;

A01 30/05/2019 100

A01 20/06/2019 110

A01 27/06/2019 120

A02 30/05/2019 10

A02 20/06/2019 20

A02 27/06/2019 30

;

data table2;

infile cards;

informat account $3. date ddmmyy10. segment $2. koef 8.;

input account--koef;

cards;

A01 30/05/2019 RB 0.26

A01 30/05/2019 KB 0.74

A02 30/05/2019 RB 0.34

A02 30/05/2019 KB 0.66

A01 20/06/2019 RB 0.26

A01 20/06/2019 KB 0.74

A02 20/06/2019 RB 0.34

A02 20/06/2019 KB 0.66

A01 27/06/2019 RB 0.26

A01 27/06/2019 KB 0.74

A02 27/06/2019 RB 0.34

A02 27/06/2019 KB 0.66

;

This is what I need:

data table3;

infile cards;

informat account $3. sum1-sum3 8. koef1-koef3 8. segment1-segment3 $2.;

input account--segment3;

cards;

A01 100 110 120 0.26 0.26 0.26 RB RB RB

A01 100 110 120 0.74 0.74 0.74 KB KB KB

A02 10 20 30 0.34 0.34 0.34 RB RB RB

A02 10 20 30 0.66 0.66 0.66 KB KB KB

;

Thank you

BR Oleg

[KachiM · Accepted solution]

The solution lies on dynamically determining the BY-Group Size. For simplicity, it is assumed to be 3.

The steps are:

[1] sort Table1 by account and date. sort table2 by account, segment and date.

[2] use double DoW loop. In the first-loop, collect the sums into an array per account.

[3] In the second loop, collect segments into an array and koefs into another array.

[4] When the By Group = 3 output the collected array elements.

data table1;
infile cards;
informat account $3. date ddmmyy10. sum 8.;
input account--sum;
cards;
A01 30/05/2019 100
A01 20/06/2019 110
A01 27/06/2019 120
A02 30/05/2019 10
A02 20/06/2019 20
A02 27/06/2019 30
;
run;
proc sort data = table1 out = t1;
by account date;
run;



data table2;
infile cards;
informat account $3. date ddmmyy10. segment $2. koef 8.;
input account--koef;
cards;
A01 30/05/2019 RB 0.26
A01 30/05/2019 KB 0.74
A02 30/05/2019 RB 0.34
A02 30/05/2019 KB 0.66
A01 20/06/2019 RB 0.26
A01 20/06/2019 KB 0.74
A02 20/06/2019 RB 0.34
A02 20/06/2019 KB 0.66
A01 27/06/2019 RB 0.26
A01 27/06/2019 KB 0.74
A02 27/06/2019 RB 0.34
A02 27/06/2019 KB 0.66
;
run;

proc sort data = table2 out=tab2;
by account segment date;
run;



data want;
   do i = 1 by 1 until(last.account);
      set t1;
      by account;
      array su sum1 - sum3;
      su[i] = sum;
   end;
   do i = 1 by 1 until(last.account);
      set tab2;
      by account;
      array k koef1-koef3;
      array s $2 segment1 - segment3;
      j = mod(i,3); if j = 0 then j = 3;
      k[j] = koef;
      s[j] = segment;
      if j = 3 then output;
   end;
drop i j sum date koef segment;
run;