topic Re: SAS Merge in SAS Programming

SAS Merge

zimcom — Sat, 08 Sep 2018 16:23:00 GMT

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!

Re: SAS Merge

Reeza — Sat, 08 Sep 2018 16:28:08 GMT

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!

Re: SAS Merge

Kurt_Bremser — Sat, 08 Sep 2018 16:29:35 GMT

What type is the variable subjecf?

Re: SAS Merge

VDD — Sat, 08 Sep 2018 16:34:09 GMT

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

Re: SAS Merge

KachiM — Sat, 08 Sep 2018 16:46:20 GMT

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

If yes, your output is OK.

Re: SAS Merge

VDD — Sat, 08 Sep 2018 16:50:46 GMT

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

Re: SAS Merge

Kurt_Bremser — Sat, 08 Sep 2018 16:51:28 GMT

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.

Re: SAS Merge

zimcom — Sat, 08 Sep 2018 16:55:04 GMT

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

Re: SAS Merge

zimcom — Sat, 08 Sep 2018 16:57:05 GMT

@Reeza

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

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

Re: SAS Merge

VDD — Sat, 08 Sep 2018 16:57:40 GMT

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

Re: SAS Merge

zimcom — Sat, 08 Sep 2018 16:59:56 GMT

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

Re: SAS Merge

zimcom — Sat, 08 Sep 2018 17:03:28 GMT

@Kurt_Bremser how can I check "alignment" then?

Re: SAS Merge

zimcom — Sat, 08 Sep 2018 17:09:07 GMT

@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

Re: SAS Merge

VDD — Sat, 08 Sep 2018 17:12:30 GMT

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

Re: SAS Merge

Astounding — Sat, 08 Sep 2018 23:43:58 GMT

Before you sort and merge, here is a statement you should apply to both data sets separately:

subject = compress(subject, , 'kd');

This will keep only the digits, and remove all other characters that may have crept into SUBJECT. (As was mentioned earlier, there may be stray characters that don't appear when you examine the data such as a tab character.) After that, you should be able to sort and merge with no problem.

Be sure to keep both commas in the key statement.

Re: SAS Merge

Kurt_Bremser — Sun, 09 Sep 2018 05:57:26 GMT

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