Combining

[ranikeka]

Hi

I have sheet 1 and sheet 2 in excel file.
I want sheet 1 main info to corresponding observations in sheet 2.
I have created datasets in sas and need to combine. So I have used merge but the main info isn’t have corresponding values in sheet2?

For example:
Sheet 1 - one observation corresponds to 40 etc.. observations in sheet 2. And so on
Like this I have numerous excel files which I combined into single dataset.
Could you please help with me this.

Thanks

[Kurt_Bremser]

Please give us an example for the two datasets after import into SAS. Post the datasets as data steps with datalines, and post the code into a window opened with the "little running man" button.

[cass5957]

Hi there!

You might have some luck using "MERGE" in the DATA step.

DATA WORK.DataSet;

MERGE Set.Sheet1

              Set.Sheet2;

RUN;

You also might need to sort the data before merging to make sure the variables so everything matches up. 

Hope this helps!

[ranikeka]

HI

Thanks for the suggestion. I dont have same variable names thats the problem. Here is my sample file to understand better. Any help appreciated.

sheet 1

abc	def	ghi
1	a	a1
2	b	a2
3	c	a3
4	d	a4

sheet 2

abc	def	ghi	jkl	mno	pqr
1	a	a1	xxxx	rrrr	tttt
1	a	a1	vvvvv	hhhh	tttt
1	a	a1	nnnnn	ggg	gg
1	a	a1	sssss	bbb	jj
1	a	a1	eeeee	ccc	kk
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
3	c	a3	fffff	tttt	uuuu
3	c	a3	pppp	qqqq	eeee
3	c	a3	tttt	ggg	tttt
3	c	a3	lll	ppp	iii

[Kurt_Bremser]

See this:

data sheet1;
input abc $ def $ ghi $;
datalines;
1  a a1
2  b a2
3  c a3
4  d a4
;

data sheet2;
input abc $ def $ ghi $ jkl $ mno $ pqr $;
datalines;
1  a a1  xxxx  rrrr  tttt
1  a a1  vvvvv hhhh  tttt
1  a a1  nnnnn ggg gg
1  a a1  sssss bbb jj
1  a a1  eeeee ccc kk
2  b a2  tttt  hhhh  nnn
2  b a2  tttt  hhhh  nnn
2  b a2  tttt  hhhh  nnn
3  c a3  fffff tttt  uuuu
3  c a3  pppp  qqqq  eeee
3  c a3  tttt  ggg tttt
3  c a3  lll ppp iii
;

data want;
merge
  sheet1
  sheet2
;
by abc def ghi;
run;

proc print data=want noobs;
run;

Result:

abc	def	ghi	jkl	mno	pqr
1	a	a1	xxxx	rrrr	tttt
1	a	a1	vvvvv	hhhh	tttt
1	a	a1	nnnnn	ggg	gg
1	a	a1	sssss	bbb	jj
1	a	a1	eeeee	ccc	kk
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
3	c	a3	fffff	tttt	uuuu
3	c	a3	pppp	qqqq	eeee
3	c	a3	tttt	ggg	tttt
3	c	a3	lll	ppp	iii
4	d	a4	 	 	 

If that does not meet your expectations, please describe in detail where it differs.

[ranikeka]

Many Thanks

can i use same code for below one as well

sheet1

abc	def	ghi
1	a	a1
2	b	a2
3	c	a3
4	d	a4

sheet 2

jkl	mno	pqr
xxxx	rrrr	tttt
vvvvv	hhhh	tttt
nnnnn	ggg	gg
sssss	bbb	jj
eeeee	ccc	kk
tttt	hhhh	nnn
tttt	hhhh	nnn
tttt	hhhh	nnn
fffff	tttt	uuuu
pppp	qqqq	eeee
tttt	ggg	tttt
lll	ppp	iii

I want to combine two above sheets like following

abc	def	ghi	jkl	mno	pqr
1	a	a1	xxxx	rrrr	tttt
1	a	a1	vvvvv	hhhh	tttt
1	a	a1	nnnnn	ggg	gg
1	a	a1	sssss	bbb	jj
1	a	a1	eeeee	ccc	kk
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
3	c	a3	fffff	tttt	uuuu
3	c	a3	pppp	qqqq	eeee
3	c	a3	tttt	ggg	tttt
3	c	a3	lll	ppp	iii

[Kurt_Bremser]

No, unless you can define a clear rule for joining that uses the content in the available variables, or other values, like observation numbers (obs 1 from sheet1 join to obs 1-4 of sheet2, obs 2 from sheet1 to obs 5-8 from sheet2, and so on).

[mkeintz]

---

@ranikeka wrote:

Many Thanks

can i use same code for below one as well

sheet1

abc	def	ghi
1	a	a1
2	b	a2
3	c	a3
4	d	a4

sheet 2

jkl	mno	pqr
xxxx	rrrr	tttt
vvvvv	hhhh	tttt
nnnnn	ggg	gg
sssss	bbb	jj
eeeee	ccc	kk
tttt	hhhh	nnn
tttt	hhhh	nnn
tttt	hhhh	nnn
fffff	tttt	uuuu
pppp	qqqq	eeee
tttt	ggg	tttt
lll	ppp	iii

I want to combine two above sheets like following

abc	def	ghi	jkl	mno	pqr
1	a	a1	xxxx	rrrr	tttt
1	a	a1	vvvvv	hhhh	tttt
1	a	a1	nnnnn	ggg	gg
1	a	a1	sssss	bbb	jj
1	a	a1	eeeee	ccc	kk
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
2	b	a2	tttt	hhhh	nnn
3	c	a3	fffff	tttt	uuuu
3	c	a3	pppp	qqqq	eeee
3	c	a3	tttt	ggg	tttt
3	c	a3	lll	ppp	iii

---

In the absence of an apparent matching rule, it looks like you are matching 1 record in sheet1 to a batch of 4 records in sheet2, in order encountered.  If that's true, you can do the below.

But just remember - one misplaced or deleted or duplicated record in either sheet will generate unwanted results, and there will be no obvious way to recognize that outcome.

data sheet1;
input abc $ def $ ghi $;
datalines;
1  a a1
2  b a2
3  c a3
4  d a4
;

data sheet2;
input jkl $ mno $ pqr $;
datalines;
xxxx	rrrr	tttt
vvvvv	hhhh	tttt
nnnnn	ggg	gg
sssss	bbb	jj
eeeee	ccc	kk
tttt	hhhh	nnn
tttt	hhhh	nnn
tttt	hhhh	nnn
fffff	tttt	uuuu
pppp	qqqq	eeee
tttt	ggg	tttt
lll	ppp	iii
run;

data want;
  set sheet1;
  do i=1 to 4; set sheet2; output; end;
run;

Note the log of this (below) reports reading 4 observations from sheet1, but that 4th obs (abc=4) is not on the output dataset.  That's because, with the 4th sheet1 record in hand, the program attempts to read the 13th from sheet2.  But there is no 13th record in sheet2, so the data step stops, with no further processing - i.e. no outputting of the record.

NOTE: There were 4 observations read from the data set WORK.SHEET1.
NOTE: There were 12 observations read from the data set WORK.SHEET2.
NOTE: The data set WORK.WANT has 12 observations and 7 variables.