topic Re: joining tables: a version of many to one?? in New SAS User

joining tables: a version of many to one??

drshashlik — Thu, 23 Jul 2020 03:30:17 GMT

Hi SAS users,

I need to join two tables, and no matter which way I go, I get many blank cells.

Table 1 has many rows with events and dates for each event.

Table 2 has 41 rows with inflation index by year and quarter for 10 years.

I need a table that has inflation value matched to each date. For example, all events occuring between January 1, 2010 and March 31, 2010 should have the inflation value for 2010Q1 in the new column.

My attempts at using proc sql (including left join) to join the tables are giving me thousands of blank cells in the inflation column.

Your help is, as always, appreciated!

Re: joining tables: a version of many to one??

drshashlik — Thu, 23 Jul 2020 04:40:30 GMT

Here is my code:
This first set of code produces the correct number of rows, but the columns from table 'cpi' are blank after the first month of observations. Everything later than 2 january 2010 is blank.
PROC SQL;
CREATE TABLE rx_cpi AS
SELECT A.*, B.* FROM rx_q A full JOIN CPI B ON A.qtr_a = B.date_cpi ;QUIT;
/*n=48,289,242*/

This version of the table removed thousands of rows.
proc sql; create table test as select a.*, b.* from r_rx as a, cpi as b where a.date_dispensed=b.date_cpi ; quit;
/*n=432,057*/

Re: joining tables: a version of many to one??

Kurt_Bremser — Thu, 23 Jul 2020 09:47:26 GMT

Please supply example data in usable form (data steps with datalines), so we have something to test against, and know all attributes of your columns.

Use the "little running man" button (right next to the one indicated, which is for logs) to post the codes:

Mind that

SELECT A.*, B.*

is considered bad practice, as you have no control which variable is used if columns appear in both datasets (which will also cause a WARNING)

Re: joining tables: a version of many to one??

ballardw — Thu, 23 Jul 2020 14:51:44 GMT

With a variable name like qtr_a I might expect the value to be a date, especially since you are matching it on date_cpi, but it might be the qtr_a represents the first day of a quarter, such as 1 Jan 2010 and so there would be no matches for date_cpi if the values represent any day of the year.

A complete guess of a possible solution might be something like

ON A.qtr_a = intnx('quarter', B.date_cpi,0,'B') ;

which would shift the b.date_cpi value to the first day of the calendar quarter for the comparison.

You should verify what the values of A.qtr_a look like before attempting this though.

We know absolutely nothing about what/when/why any of your date_dispensed or date_cpi were collected. You would have to know if it makes sense to compare those.

One might also suspect there maybe should be some other identifier involved in the Join or Where criteria.