BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
luvscandy27
Quartz | Level 8

I am trying to join the two tables below together however, my denominator values do not come over. I feel like I am missing something but I just can't seem to 

find out what I'm missing. Any help would be greatly appreciated. My code is below. 

 

proc import datafile = "/home/luvscandy270/New Cases.xlsx" 
DBMS = xlsx out = cases replace ;
run;

proc import datafile = "/home/luvscandy270/QTR.xlsx"
DBMS = xlsx out = denom replace ;
run;

proc sql;
create table combine as
select a.branch, a.month, a.year, first_sum, denominator
from cases A left join denom B
on A.branch = B.branch and A.year = B.year and A.month=B.month ;
quit;

 

1 ACCEPTED SOLUTION

Accepted Solutions
tarheel13
Rhodochrosite | Level 12

Do you realize in your cases dataset the year is always 2020? In qtr dataset, you do not have any years that are 2020. That's why your denominator isn't getting populated. 

View solution in original post

10 REPLIES 10
ballardw
Super User

I don't open XLSX from unknown sources.

 

Import makes guesses about variable properties for each file and after examining a very few rows. So variables can be of different types even though you don't think they should.

 

So go back to the individual SAS data sets and see if the values for branch, year and month are actually same.

First thing I would likely check is the Branch as that is one likely to have character values in one set and numeric in another. Which could mean they do not compare as equal.

First step: Run proc contents on both and examine type and length of values.

 

Another concern is if either of the variables name "year" or "month" are actually treated as date variables. If versions in both sets are numeric and one has a format of BESTxx and the other is a date format (too many to list but if the format name has part of the name as YY MM MON or similar, then that is likely a cause.

luvscandy27
Quartz | Level 8

Below please find the output from proc contents. I think it should be ok? 

 

Cases        
# Variable Type Len Format
1 BRANCH Char 4 $4.00
3 FIRST_Sum Num 8  
5 MONTH Num 8  
2 MONYR Num 8 MONYY7.
4 YEAR Num 8  

 

Denom          
# Variable Type Len Format Informat
1 BRANCH Char 4    
4 DENOMINATOR Num 8 8 BEST12.
2 MONTH Num 8 8 BEST12.
3 YEAR Num 8 8 BEST12.
           
PGStats
Opal | Level 21

Do you get any error or warning messages ? What happens if you replace the left join with an inner join?

PG
luvscandy27
Quartz | Level 8

I do not receive any type of errors, the data step completes but the dataset does not have any observations. I've tried switching the join type and the same thing happens. 

tarheel13
Rhodochrosite | Level 12

Can you post some output or what you see in the log? 

luvscandy27
Quartz | Level 8

The log does not have any errors. I looks as if it worked fine but the combined dataset does not have any observations.

tarheel13
Rhodochrosite | Level 12

I ran your code. Denom column is missing. That means no matches were found. SQL is going to make the column missing unless there's a match. 

tarheel13
Rhodochrosite | Level 12

Do you realize in your cases dataset the year is always 2020? In qtr dataset, you do not have any years that are 2020. That's why your denominator isn't getting populated. 

luvscandy27
Quartz | Level 8

OMG!!! You are absolutely correct. I forgot its a new year and had not updated my denominators. The whole time I was thinking my format was incorrect. Thank you 

so much. 

tarheel13
Rhodochrosite | Level 12

It's always something stupid. At least it wasn't anything wrong with your SQL syntax lol. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1007 views
  • 1 like
  • 4 in conversation