Help using Base SAS procedures

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

Reply
Contributor
Posts: 61

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


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;

Super User
Posts: 5,071

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

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.

Contributor
Posts: 61

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

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

Super User
Posts: 5,071

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

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.

Super User
Super User
Posts: 6,495

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

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

Super Contributor
Super Contributor
Posts: 3,174

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 215 views
  • 3 likes
  • 4 in conversation