SAS Merge

[zimcom]

Hi Community,

I have a very simple merge code:

proc sort data = table1; by subject; run;
proc sort data = table2; by subject; run;
data table12; merge table1 (in=a) table2; by subject; if a; run;

table1

SUBJECT	ae_start	ae_end	ae_dur
118118	31-Mar-15	31-Mar-15	1
118872	19-Apr-15	09-May-15	21

table2

Trial	SUBJECT	age	sex
abc	118118	78	male
abc	118151	60	male
abc	118377	73	male
abc	118682	84	male
abc	118730	60	male
abc	118872	81	female

after merge

SUBJECT	ae_start	ae_end	ae_dur	Trial	age	sex
118118	31-Mar-15	31-Mar-15	1
118872	19-Apr-15	09-May-15	21

somehow, data from table2 data won't show in the merged table, I double checked the column property, every attribute (Type, Length, Format, and Informat are all teh same), what would be the other cause that failed this merge?

As always, thank you all for the past support! 

[Reeza]

Please show the log from the data. As you mentioned, you would need to check if the merge variable, subject is the same. If you've already checked formats and lengths, your next step is to check them with a hex format. Sometimes there's invisible characters such as spaces or line returns. If thats the case you can remove them with a COMPRESS() function. If they're numeric you may also be running into precision issues and covering to a character may help resolve this. 

And if you can't get the MERGE to work, try a SQL join instead. 

---

@zimcom wrote:

Hi Community,

I have a very simple merge code:

proc sort data = table1; by subject; run;
proc sort data = table2; by subject; run;
data table12; merge table1 (in=a) table2; by subject; if a; run;

table1

SUBJECT	ae_start	ae_end	ae_dur
118118	31-Mar-15	31-Mar-15	1
118872	19-Apr-15	09-May-15	21

table2

Trial	SUBJECT	age	sex
abc	118118	78	male
abc	118151	60	male
abc	118377	73	male
abc	118682	84	male
abc	118730	60	male
abc	118872	81	female

after merge

SUBJECT	ae_start	ae_end	ae_dur	Trial	age	sex
118118	31-Mar-15	31-Mar-15	1
118872	19-Apr-15	09-May-15	21

somehow, data from table2 data won't show in the merged table, I double checked the column property, every attribute (Type, Length, Format, and Informat are all teh same), what would be the other cause that failed this merge?

As always, thank you all for the past support! 

---

[zimcom]

@Reeza

The log show everything is fine, there is no error, no warning msg.

Subject is Type (Text) Length (8) Format ($8.) Informat ($8.) 

[Kurt_Bremser]

What type is the variable subjecf?

[zimcom]

@Kurt_BremserSubject is Type (Text) Length (8) Format ($8.) Informat ($8.) 

[VDD]

data table12; merge table1 (in=a) table2(in=b); by subject; if a and b; run;

[zimcom]

@VDD tried, if I do 'data table12; merge table1 (in=a) table2(in=b); by subject; if a and b; run;' 

log shows

NOTE: There were 46 observations read from the data set WORK.TABLE1.
NOTE: There were 280 observations read from the data set WORK.TABLE2.
NOTE: The data set WORK.TABLE12 has 0 observations and 13 variables.

[KachiM]

Are you not asking for those SUBJECT found in TABLE1 and matching those in TABLE2?

If yes, your output is OK.

[Kurt_Bremser]

Since there are "obvious" matches, the variables from table2 should not be missing. So something must be different. If the variables are character, but longer than 6 characters, alignment might be an issue. If numeric, a numeric precision problem might be happening.

[zimcom]

@Kurt_Bremser how can I check "alignment" then?

[Kurt_Bremser]

---

@zimcom wrote:

@Kurt_Bremser how can I check "alignment" then?

---

Do this:

length _subject $16;
_subject = put(subject,$hex16.); 

in data steps on both tables and compare the new variables.

[VDD]

data a;
input SUBJECT (ae_start ae_end) (:date9.) ae_dur;
cards;
118118 31-Mar-15 31-Mar-15 1
118872 19-Apr-15 09-May-15 21
;
data b;
input SUBJECT age sex $;
cards;
118118 78 male
118151 60 male
118377 73 male
118682 84 male
118730 60 male
118872 81 female
;
proc sort data=a;
by subject;
run;
proc sort data=b;
by subject;
run;
data c;
merge a(in=a) b(in=b);
by subject;
if a and b;
run;

Obs SUBJECT ae_start ae_end ae_dur age sex
1 118118 20178 20178 1 78 male
2 118872 20197 20217 21 81 female

[zimcom]

@VDD Thank you!!!

I know this way, it will work, since when you read in data again, all the variables have the same Type, Length, 

But I need to figure out where my problem is and I tried 'if a and b', there are 0 result 

[VDD]

The by subject variable is not matching in both of you data tables.  Something is different if the merge is not working.

[VDD]

The problem is in your merge.  You only asked if in a, you did not request the merge to keep b.