topic Combining in New SAS User

Combining

ranikeka — Mon, 07 Dec 2020 18:35:42 GMT

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

Re: Combining

Kurt_Bremser — Mon, 07 Dec 2020 18:51:08 GMT

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.

Re: Combining

cass5957 — Sun, 13 Dec 2020 22:07:20 GMT

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!

Re: Combining

ranikeka — Tue, 15 Dec 2020 10:36:06 GMT

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

Re: Combining

Kurt_Bremser — Tue, 15 Dec 2020 11:28:01 GMT

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.

Re: Combining

ranikeka — Tue, 15 Dec 2020 12:55:44 GMT

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

Re: Combining

Kurt_Bremser — Tue, 15 Dec 2020 13:08:18 GMT

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).

Re: Combining

mkeintz — Tue, 15 Dec 2020 16:18:28 GMT

@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.