topic Re: Joining Tables in SAS Programming

Joining Tables

luvscandy27 — Wed, 27 Jan 2021 13:04:35 GMT

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;

Re: Joining Tables

ballardw — Wed, 27 Jan 2021 15:17:28 GMT

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.

Re: Joining Tables

PGStats — Wed, 27 Jan 2021 16:10:15 GMT

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

Re: Joining Tables

tarheel13 — Wed, 27 Jan 2021 20:00:47 GMT

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

Re: Joining Tables

luvscandy27 — Fri, 29 Jan 2021 16:19:03 GMT

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.

Re: Joining Tables

luvscandy27 — Fri, 29 Jan 2021 16:21:00 GMT

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.

Re: Joining Tables

luvscandy27 — Fri, 29 Jan 2021 16:28:02 GMT

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

Re: Joining Tables

tarheel13 — Fri, 29 Jan 2021 16:55:07 GMT

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.

Re: Joining Tables

tarheel13 — Fri, 29 Jan 2021 16:59:41 GMT

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.

Re: Joining Tables

luvscandy27 — Fri, 29 Jan 2021 17:24:10 GMT

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.

Re: Joining Tables

tarheel13 — Fri, 29 Jan 2021 20:45:00 GMT

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