SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

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!

Accepted Solutions
Solution
‎11-01-2017 12:20 PM
Contributor
Posts: 26

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

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 93

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

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

Contributor
Posts: 26

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

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;
Contributor
Posts: 26

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

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.
Super User
Posts: 23,237

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

Post your code and log
Contributor
Posts: 26

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

 
Attachment
Super User
Posts: 23,237

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

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

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

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.
Super User
Posts: 23,237

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

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. 
Solution
‎11-01-2017 12:20 PM
Contributor
Posts: 26

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

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 366 views
  • 1 like
  • 3 in conversation