topic How to merge values horizontally from second dataset in SAS Programming

How to merge values horizontally from second dataset

kajal_30 — Wed, 26 Jun 2024 17:01:11 GMT

ok so I have 2 datasets and want dataset as below. Can you please help how to create want dataset using 1 and 2 .

dataset1
ID	name
1	peter
2	john
3	muse
4	peena
5	tina

dataset 2
ID	Value
1	cbz1
2	cbz2
2	cbz3
2	cbz4
3	cbz5
3	cbz6
3	cbz7
4	cbz8
4	cbz9
4	cbz10
4	cbz11
5	cbz12

want
ID	name	value1	value2	value3	value4
1	peter	cbz1	.	.	.
2	john	cbz2	cbz3	cbz4	.
3	muse	cbz5	cbz6	cbz7	.
4	peena	cbz8	cbz9	cbz10	cbz11
5	tina	cbz12	.	.	.

Regards

Kajal

Re: How to merge values horizontally from second dataset

data_null__ — Wed, 26 Jun 2024 17:46:15 GMT

Many ways but transposing TWO has the advantage of creating as many VALUE variables as needed, you don't have to count them.

data one;
   infile cards expandtabs;
   input id name $;
   cards;
1	peter
2	john
3	muse
4	peena
5	tina
;;;;
   run; 

data two;
   infile cards expandtabs;
   input id value $ ;
   cards;
1	cbz1
2	cbz2
2	cbz3
2	cbz4
3	cbz5
3	cbz6
3	cbz7
4	cbz8
4	cbz9
4	cbz10
4	cbz11
5	cbz12
;;;;
   run;
   
proc transpose data=two out=wide(drop=_:) prefix=Value;
   by id;
   var value;
   run;
   
data want;
   merge one wide;
   by id;
   run;

proc print;
   run;

Re: How to merge values horizontally from second dataset

Tom — Wed, 26 Jun 2024 17:33:41 GMT

The easiest thing is to just do a normal MERGE and leave the result alone.

First let's convert your listings into actual dataset. (Hint you could have shared the data this way to begin with to make it easier for others to help you.)

data dataset1;
  input ID name $;
cards;
1  peter
2  john
3  muse
4  peena
5  tina
;

data dataset2;
  input ID Value $;
cards;
1  cbz1
2  cbz2
2  cbz3
2  cbz4
3  cbz5
3  cbz6
3  cbz7
4  cbz8
4  cbz9
4  cbz10
4  cbz11
5  cbz12
;

Now just merge the data. You might want to add a counter variable to number the observations within each value of ID.

data want;
  merge dataset1 dataset2;
  by id;
  if first.id then row=1;
  else row+1;
run;

If you want that final listing output then use PROC REPORT with an across variable.

proc report data=want;
  column id name value,row dummy;
  define id / group;
  define name / group;
  define row / across ' ';
  define dummy / computed noprint;
  compute dummy; dummy=0; endcomp;
run;

Result

If you did need the data stored into multiple different variables in that wide format then just use PROC TRANSPOSE.

proc transpose data=want out=wide(drop=_name_) prefix=Value;
  by id name;
  id row;
  var value;
run;

Result

Re: How to merge values horizontally from second dataset

kajal_30 — Wed, 26 Jun 2024 18:58:12 GMT

Thank you actually I overestimated myself. I thought I would be able to handle it but couldn't .

Actually there are more than 1 column along with name so not sure how to handle multiple colums.

Eg:

dataset 1 has 10 columns and dataset 2 has 12 columns.

Not sure how to include rest of the columns in the same code you provided.

Regards

Kajal

Re: How to merge values horizontally from second dataset

ballardw — Wed, 26 Jun 2024 22:32:00 GMT

@kajal_30 wrote:

Thank you actually I overestimated myself. I thought I would be able to handle it but couldn't .

Actually there are more than 1 column along with name so not sure how to handle multiple colums.

Eg:

dataset 1 has 10 columns and dataset 2 has 12 columns.

Not sure how to include rest of the columns in the same code you provided.

Regards

Kajal

What you can do with one variable you can do with other variables BUT naming will be different. So Transpose each of the additional variables in dataset2 into its own data set and merge all of them on the name value if you think you really need a data set.

The Report approach @Tom provided would with with multiple variables.

data dataset1;
  input ID name $;
cards;
1  peter
2  john
3  muse
4  peena
5  tina
;

data dataset2;
  input ID Value $  x $;
cards;
1  cbz1   abc
2  cbz2   pdq
2  cbz3   xyz
2  cbz4  abc
3  cbz5  pdq
3  cbz6  xyz
3  cbz7  abc
4  cbz8  pdq
4  cbz9  xyz
4  cbz10  abc
4  cbz11  pdq
5  cbz12  xyz
;

data want;
  merge dataset1 dataset2;
  by id;
  if first.id then row=1;
  else row+1;
run;

proc report data=want;
  column id name value,row x,row dummy;
  define id / group;
  define name / group;
  define row / across ' ';

  define dummy / computed noprint;
  compute dummy; dummy=0; endcomp;
run;