BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
UPEN
Obsidian | Level 7

I have datasets like

 

Obs

Num1

Num2

Num3

 1

1

.

1

2

2

5

2

3

3

5

.

4

4

5

4

5

6

7

8

 

 

Num6

Num7

 

3

4

 

4

2

 

6

.

 

2

6

   

 Output should be

Obs Num1 Num2 Num3 Num6 Num7 1 2 3 4 5
1.134
25242
35.6.
45426
678
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@HB This is incorrect you can merge without a BY statement if you're doing a side by side merge.

 

data want;
merge file1 file2;
run;

proc print data=want;run;

 

 

SAS Output

The SAS System

Obs obs var1 var2 var3 var4 var5
1 1 1 . 1 3 4
2 2 2 5 2 4 2
3 3 3 5 . 6 .
4 4 4 5 4 2 6
5 5 6 7 8 . .

View solution in original post

12 REPLIES 12
Shmuel
Garnet | Level 18

just do:

 

data want;

merge table1 table2;

by;

run;

UPEN
Obsidian | Level 7

If I do merge data will be appended after the first dataset, but I want it along with the first dataset

Shmuel
Garnet | Level 18
merge will do what you expect. Just try it and check result.
Reeza
Super User

@ujwala wrote:

If I do merge data will be appended after the first dataset, but I want it along with the first dataset


Try it. If it doesn't work, post the code and log. The premise is correct, so if it doesn't work, either the situation is not as described or your code is incorrect.

 

 

HB
Barite | Level 11 HB
Barite | Level 11

Your data is going to need a key to be merged with.

 

I'll assume a variable called obs (which is a bad variable name!)

 

Doing this:

data file1;
	input obs var1 var2 var3;
	datalines;
1 1 . 1
2 2 5 2
3 3 5 .
4 4 5 4
5 6 7 8
;

data file2;
	input obs var4 var5;
	datalines;
1 3 4 
2 4 2
3 6 .
4 2 6
;

proc sort data=file1 out=file1_sorted;
	by obs;
run;

proc sort data=file2 out= file2_sorted;
	by obs;
run;

* the (in = a) and (if a) function like a left join;
data combined;
	merge file1_sorted (in = a) file2_sorted;
	by obs; 
	if a;
run;

gives us this:

obs	var1	var2	var3	var4	var5
1	1		1	3	4
2	2	5	2	4	2
3	3	5		6	
4	4	5	4	2	6
5	6	7	8		

HTH.

Reeza
Super User

@HB This is incorrect you can merge without a BY statement if you're doing a side by side merge.

 

data want;
merge file1 file2;
run;

proc print data=want;run;

 

 

SAS Output

The SAS System

Obs obs var1 var2 var3 var4 var5
1 1 1 . 1 3 4
2 2 2 5 2 4 2
3 3 3 5 . 6 .
4 4 4 5 4 2 6
5 5 6 7 8 . .
HB
Barite | Level 11 HB
Barite | Level 11

@Reeza I don't consider it incorrect. It may be less terse, but the verbosity helps me remember all the components to the statement.

 

It's also working code.

Reeza
Super User

@HB wrote:

Your data is going to need a key to be merged with.

 

 


@HB the statement above is what I would consider incorrect. No key is needed in this situation, you can add one, but it's not required. Your code/solution does work, and in that respect is correct. 

HB
Barite | Level 11 HB
Barite | Level 11
Without a key in the original data, there is an assumption of order. Without that implied order, which might be a problem down the line in the real world, knowing how the first dataset relates to the second dataset becomes extremely problematic. As they are, they may be able to be smashed together. Get them out of order and a key will be needed. In my opinion, it's better to set it up with a key and use a key. YMMV. You're right.
Astounding
PROC Star

If you know that your first data set is the one with more observations, you could also code it this way:

 

data want;

set have1;

if done=0 then set have2 end=done;

output;

call missing (of _all_);

run;

 

mnjtrana
Pyrite | Level 9

 

Just merge them, without any by statement.

 

data want;
merge data1 data2;
run;

 


Cheers from India!

Manjeet
Patrick
Opal | Level 21

@UPEN

May be after all the discussion in this tread you also want to have a read of the docu to get a bit more insight of the options.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p15jvywi5avt3cn1bee8...

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 5403 views
  • 1 like
  • 7 in conversation