Joining Tables

[luvscandy27]

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;

[ballardw]

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]

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]

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

[luvscandy27]

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]

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

[luvscandy27]

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

[tarheel13]

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 · Accepted solution]

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]

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]

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