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;

Are you just looking for a SET statement with a BY statement so that the values from the two datasets are interleaved based on the values of the BY variables? — Tom, May 23 '19 at 13:59
You mean `data aa; set HAVE1 HAVE2; by id; run;` That doesn't work as I expected — DarkousPl, May 23 '19 at 14:19
Then please explain in more detail what you are trying to create. Do you just want to replicated the value of WARTOSC 20 times? — Tom, May 23 '19 at 14:58
I want data like in example code in DATA WANT. I want insert data from HAVE2 to HAVE1 - and value from field WARTOSC (from HAVE2) disperse for col1-col19 in HAVE1, not set only WARTOSC to HAVE1. — DarkousPl, May 23 '19 at 15:02
For future questions, you don't need all `20` to get the point across, `5` would have been fine and demonstrated the underlying problem more succinctly. — Richard, May 23 '19 at 15:19

score 1 · Answer 1 · answered May 23 '19 at 15:17

Your wanted table is quite peculiar, you might be better off producing a report instead of a data set that you might simply proc print.

Regardless, the step will, for have2, require transformation of name and replication of wartosc.

For example:

data want (drop=wartosc);
  set have1 end=end1;
  output;

  if not end2 then 
    set have2(rename=id=id2) end=end2;

  if id = id2 then do;
    array col col1-col20;
    do over col; col=wartosc; end;
    name = catx('_', name, id);
    output;
  end;

run;

You might need some more logic if the case of want2 having more rows than want1 can occur.

Richard, your solution also is a very interesting, thanks! – DarkousPl May 23 '19 at 15:59 — DarkousPl, May 23 '19 at 15:59

score 1 · Accepted Answer · answered May 23 '19 at 15:36

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

How I can set data with specific system

2 Answers2