BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6


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;

5 REPLIES 5
Astounding
PROC Star

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.

chandler
Fluorite | Level 6

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

Astounding
PROC Star

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.

Tom
Super User Tom
Super User

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

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 935 views
  • 3 likes
  • 4 in conversation