SAS set contains missing values when trying to call it into a merge

[kmj636]

HI, Here is my issue: I create my SAS Data set from an excel file and all VARs and Obs are present. I attempt to merge my new SAS Data set with an existing SAS Data set and while the merge processes without errors not only is the merge data set all missing values but now my recently created SAS Data set (from the excel file) is displaying missing values as well. I ran a putlog to display the observations and all are missing. I have to assume something is getting corrupted but I do not know at what point. Someone please help. I am not sure how to troubleshoot this issue. Thank you for your time!

[niam]

Your original dataset should not change. can you post your code? 

[kmj636]

Code for creating SAS data set: proc import datafile='I:/HCBS Sample File/HCBS CostSample.xlsx' out=work.SFB dbms=xlsx replace; sheet="Sheet1"; getnames=yes; run; libname all 'C:\MergeHCBS'; Data all.FB; length lastname $18 firstname $15; set work.SFB (rename=(LNAME=lastname FNAME=firstname)); format lastname $18. firstname $15. DOB date9.; keep lastname firstname DOB; run; Proc contents data=all.FB; run; Everything works fine here and then ... Code for merge libname all 'C:/MergeHCBS'; Proc sort data=all.fb; by lastname firstname DOB; run; %let path=C:/A15; libname HCBS "&path"; Data work.BS (keep=firstname lastname DOB); set HCBS.A15 (keep= firstname lastname bth_date placeser); format DOB date9.; DOB=input(bth_date, ??mmddyy8.); if placeser='1'; run; proc sort data=work.BS; by lastname firstname DOB; run; Data work.BSIN (keep=firstname lastname DOB); set HCBS.AL15 (keep= firstname lastname bth_date); format DOB date9.; DOB=input(bth_date, ??mmddyy8.); run; proc sort data=work.BSIN; by lastname firstname DOB; run; Data MergeOut; merge all.fb (in=F) work.bs (in=B); by lastname firstname DOB; if F and B; run; proc print data=MergeOut (obs=10); run; title "Match Merge Inpatient on last first and DOB"; Data MergeIn; merge all.fb (in=F) work.bsin (in=IN); by lastname firstname DOB; if F=1 and IN=1; run; proc print data=MergeIn (obs=10); run;

[kmj636]

Give me a second to upload the file so it is easier to view. I really apologize we are having problems with our electricity and internet.

[Reeza]

Post your code and log

[Reeza]

Post a proc contents on your original data. You will want to up/lowcase the names to ensure they're all the same case.

[kmj636]

Thank you for your time. Yes, I ran a proc contents on all data sets before the merge. All data sets have the same var length and type and contain observations. It is after the merge that the data set converted from excel contains all missing observations. I run a second proc contents after the merge and the data set contains the vars (as they should be) and no observations.

[Reeza]

Then you have no matches. Check on a small subset that you think should match and work that out first. If it still doesn't work, you'll need to post a small example, including sample data. It doesn't have to be real data, just representative of your data and illustrates your issue. I don't see anything wrong with your code, which makes it likely a data issue. Or skimming the code isn't pointing it out, but running it may show the issue. 

[kmj636 · Accepted solution]

I am really annoyed with myself but felt I should let everyone know (who is interested) the issue was actually with the fact that I had not deleted the blank cells in my excel file. When I was sorting the file it was (correctly) showing the missing observations first and since I was only displaying the first ten obs ... my data set was blank. This is such a silly mistake. I can't believe I had not caught it earlier. Anyway, Thank you for everyone's help. Long day yesterday.