topic Re: Why can't I merge on Key variable after PROC TRANSPOSE ? in SAS Procedures

Why can't I merge on Key variable after PROC TRANSPOSE ?

chandler — Wed, 19 Dec 2012 19:41:20 GMT

I have a dataset that I put through a PROC TRANSPOSE. Now I want to expand data and make a report, by merging in extra fields, using the new output from the PROC TRANSPOSE as the base dataset, and secondary datasets to pull extra fields, matching on account ID field. I see all the matching account IDs in the secondary tables, but when merge completes, the fields from the secondary tables show "." for missing numeric and " " for missing character values. None of the fields populated in the merged dataset, from the second, third, fourth, fifth or sixth table.

Here is my code. Any thoughts?

proc sort data=bbwhse.test_appeals2 ; /** this is the main dataset, which was output file from PROC TRANSPOSE **/

by application_id ;

run;

proc sort data=bbwhse.turn_down_david_fields ;

by application_id ;

run;

proc sort data=bbwhse.turn_down_acaps_lite_fields ;

by application_id ;

run;

proc sort data=bbwhse.turn_down_busmast_fields ;

by application_id ;

run;

proc sort data=bbwhse.turn_down_prinmast_fields ;

by application_id ;

run;

proc sort data=bbwhse.tdhist_new_judgmental_declines out=bbwhse.td_reasons nodup;

by application_id seq_file_key ;

run;

proc sort data=bbwhse.td_reasons (drop=seq_file_key state area hist_rec) nodupkey;

by application_id ;

run;

data bbwhse.appeals_processing_code_report ;

merge bbwhse.test_appeals2 (in=a) bbwhse.turn_down_david_fields (in=b) bbwhse.turn_down_acaps_lite_fields (in=c) bbwhse.turn_down_busmast_fields (in=d) bbwhse.turn_down_prinmast_fields (in=e) bbwhse.td_reasons (in=f) ;

by application_id ;

if a and b and c and d and e and f ;

run;

Re: Why can't I merge on Key variable after PROC TRANSPOSE ?

Astounding — Wed, 19 Dec 2012 19:49:20 GMT

chandler,

Assuming that the fields from the second data set are ALWAYS missing, and not just sometimes missing, you only have two possibilities.

1. The fields are actually missing in the second data set before you merge them in.

2. The same fields appear in one of the later data sets, but are missing in that later data set.

No other possibilities that I can imagine.

Good luck.

Re: Why can't I merge on Key variable after PROC TRANSPOSE ?

chandler — Wed, 19 Dec 2012 22:58:52 GMT

Thanks, Astounding. I check the other tables, and values are there. There are no duplicate fields across tables. They are not missing.

Re: Why can't I merge on Key variable after PROC TRANSPOSE ?

Tom — Thu, 20 Dec 2012 00:10:55 GMT

Get rid of the IF statement and you can probably figure where you have the mismatch in the values of your BY variables.

Re: Why can't I merge on Key variable after PROC TRANSPOSE ?

Astounding — Thu, 20 Dec 2012 00:13:08 GMT

Then you start getting into more obscure reasons. For example, perhaps another data set contains a variable named B that overwrites the in=b value. It seems like a match is there when there really is no match. But that's pretty farfetched in real life.

Basically, you are talking about an impossible result. There could be other obscure situations. For example, the actual matches could contain missing values in the additional data sets. But they could contain other nonmissing values that look like that should match but actually don't because application_id is not left-justified.

Take a single application_id where you think there should be a match. Print the observations from each data set separately for that application_id. Let us know what you find.

Re: Why can't I merge on Key variable after PROC TRANSPOSE ?

sbb — Thu, 20 Dec 2012 02:32:48 GMT

Also, consider adding some PUTLOG statements in the DATA step to echo back to your SAS log the contributing variable / value combinations. Also, consider seriously the MERGE behavior and how the files defined on the MERGE statement influence the SAS system behavior, when (already mentioned in a subsequent reply) with same-named SAS variable names.

You will be best served by doing some desk-checking (adding the PUTLOG statements) and running a sample controlled-test with a limited data subset. Or better yet, use a DATA step to generate some sample data observations with known-values and use that to exercise your SAS program/code.

Good luck!

Scott Barry

SBBWorks, Inc.