How I can set data with specific system

Question

I have little problem. As example tables HAVE1 and HAVE2 I want create table like WANT, set below specific row data from HAVE2 - to all column (since COL1 to COL19, without COL20) - and get table like WANT. How I can do?

data HAVE1;
infile DATALINES dsd missover;
input ID NAME $ COL1-COL20;
CARDS;
1, A1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 ,20
2, A2, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
3, B1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 13, 14, 15, 16, 16, 20, 21 , 21, 22 
4, B2, 1, 20, 3, 20, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 23, 22, 23
5, C1, 20, 2, 3, 4, 5, 6, 7, 8, 9, 10, 30, 12, 13, 14, 15, 16, 17, 17, 17, 17
6, C2, 1, 2, 3, 20, 5, 6, 7, 8, 02, 10, 11, 12, 30, 14, 15, 16, 17, 18, 19, 20
;run;

Data HAVE2;
infile DATALINES dsd missover;
input ID NAME $ WARTOSC;
CARDS;
1, SUM, 50000
2, SUM, 55000
3, SUM, 60000
;run;

DATA WANT;
infile DATALINES dsd missover;
input ID NAME $ COL1-COL20;
CARDS;
1, A1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 ,20
1, SUM_1    ,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000
2, A2, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
2, SUM_2, 55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000,55000
3, B1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 13, 14, 15, 16, 16, 20, 21 , 21, 22 
3, SUM_3,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000,60000
4, B2, 1, 20, 3, 20, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 23, 22, 23
5, C1, 20, 2, 3, 4, 5, 6, 7, 8, 9, 10, 30, 12, 13, 14, 15, 16, 17, 17, 17, 17
6, C2, 1, 2, 3, 20, 5, 6, 7, 8, 02, 10, 11, 12, 30, 14, 15, 16, 17, 18, 19, 20
;run;

Answer 1

So it sounds like you just need to reformat the second dataset to match what you want and them combine them. Just copy the value of WARTOSC to all of the columns and drop the original WARTOSC variable.

data HAVE1;
  infile CARDS dsd truncover;
  input ID NAME $ COL1-COL5;
CARDS;
1, A1, 1, 2, 3, 4, 5
2, A2, 1, 2, 3, 4, 5
3, B1, 3, 4, 5, 6, 7
4, B2, 1, 20, 3, 20, 5
5, C1, 20, 2, 3, 4, 5
6, C2, 1, 2, 3, 20, 5
;

data HAVE2;
  infile CARDS dsd truncover;
  input ID NAME $ WARTOSC;
CARDS;
1, SUM, 50000
2, SUM, 55000
3, SUM, 60000
;

data have2_fixed;
  set have2;
  name=catx('_',name,id);
  array col col1-col5;
  do over col ; col=wartosc; end;
  drop wartosc;
run;

data want ;
  set have1 have2_fixed;
  by id;
run;

You could actually make the changes during the merge if the datasets are large.

data want ;
  set have1 have2 (in=in2);
  by id;
  array col col1-col5;
  if in2 then do;
    name=catx('_',name,id);
    do over col ; col=wartosc; end;
  end;
  drop wartosc;
run;

Results:

Obs    ID    NAME      COL1     COL2     COL3     COL4     COL5

 1      1    A1           1        2        3        4        5
 2      1    SUM_1    50000    50000    50000    50000    50000
 3      2    A2           1        2        3        4        5
 4      2    SUM_2    55000    55000    55000    55000    55000
 5      3    B1           3        4        5        6        7
 6      3    SUM_3    60000    60000    60000    60000    60000
 7      4    B2           1       20        3       20        5
 8      5    C1          20        2        3        4        5
 9      6    C2           1        2        3       20        5