topic Re: merge tables and got empty columns in SAS Programming

Join tables and got empty columns

Songchan — Fri, 11 Jan 2019 23:59:42 GMT

Hello Guys,

I'm trying to merge two tables by cusip number and date, there are no empty values in every column, but the merged table has empty columns, what are the possible reasons?

Thank YOU

proc sql; 
     create table aa
	 as select a.*, b.*
	 from temp04 as a
	 left join temp05 as b
	 on a.cusip=b.cusip and a.datadate=b.date;
quit;

Re: merge tables and got empty columns

VDD — Fri, 11 Jan 2019 22:04:44 GMT

proc sql; 
     create table aa
	 as select a.*, b.*
	 from temp04 as a,
	 temp05 as b
	 where a.cusip=b.cusip and a.datadate=b.date;
quit;

Re: merge tables and got empty columns

Songchan — Fri, 11 Jan 2019 22:08:37 GMT

Thank you for your reply, but i need temp04 left join temp05.

Re: merge tables and got empty columns

Astounding — Fri, 11 Jan 2019 22:14:30 GMT

With a left join, this just means there are some mismatches ... CUSIPs and/or dates that appear in temp04 but don't appear in temp05.

Inspecting the data you posted, it looks like there would be plenty of mismatches. The result you have is not surprising. You need an exact match on both CUSIP and DATE.

Re: merge tables and got empty columns

Songchan — Fri, 11 Jan 2019 22:17:17 GMT

Yes, because i only need the data in temp04, temp05 is all 20-year daily data. My result shows the whole column is empty

Re: merge tables and got empty columns

ballardw — Fri, 11 Jan 2019 23:56:56 GMT

When you have two tables with the same variable name and you use Select a.*, b.* the value of the common named variable may not be coming from the table you expect.

Try using this select and see if it helps:

Select a.cusip as cusipA, b.cusip as cusipB, a.*, b.*

Re: Join tables and got empty columns

PGStats — Sat, 12 Jan 2019 06:08:24 GMT

Is the format for CUSIPs the same in both datasets? It seems to have 9 characters with leading zeros in temp04 and 8 characters without leading zeros in temp05.