PROC SQL issue - joining while creating a new variable

[DasRegulator]

Hi All,

Dumb Question:

I am currently facing an issue where the statement below confuses the dates when combining datasets. 

     PROC SQL;

           CREATE TABLE WORK.Three AS 

                SELECT T1.TR_PRICE,

                T1.TR_QUANTITY,

                T1.DT_PK_QUANTITY,

                T1.DATE,

               T2.tot_dt_pk_quantity,

                (TR_PRICE*(DT_PK_QUANTITY/tot_dt_pk_quantity)) AS TR_WEIGHTED_PRICE

           FROM WORK.One T1, WORK.Two T2

           WHERE T1.DATE=T2.DATE

           ORDER BY T1.DATE;

     RUN;

     QUIT;

In the T1 table, there are multiple values for the date (ex:40 lines of data for 01Jan2012), however in the T2 table there is only one value for the date.  Then when the new table is created, the variable DT_PK_Quantity takes the correct values for JAN 1-2, but then starts spitting out random numbers (half right, half wrong) for the variable. 

There is no error statement, nor warning statement, just a confused table as the end product.

Any help would be most appreciated!

Best,

Regulator

[ballardw]

Have you tried using T1.TR_PRICE, T1.DT_PK_QUANTITY and T2.TOT_DT_PK_QUANTITY in the calculation?

Also you may want

From WORK.ONE AS T LEFT JOIN WORK.TWO AS T2 ON T1.DATE=T2.DATE instead of your current From and WHERE clauses (guessing at what you are attempting to do).

[DasRegulator]

If I put: (t1.tr_price*(t1.dt_pk_quantity/t2.tot_dt_pk_quantity)...

then I receive an error message.

I'll try the Join statement and see what happens.

[TomKari]

Your join code looks pretty clean to me.

Is it possible that your two source tables have slightly different underlying formats for the dates, such that the readable version is equivalent, but the actual values aren't? (Like a SAS datetime value formatted to only show date).

That's all I can think of. What data management systems are the two tables in?

Tom

[DasRegulator]

Tom,

When I saw your message, I really hoped that, that was the case, but unfortunately both formats are in DATE9.

Also, the files are SAS7bdat

[TomKari]

No, this isn't a bad thing.

SAS date and time processing can be a little confusing (as can be various DBMS products). The following link is to an overview in the SAS documentation.

http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p1wj0wt2ebe2a0n1lv4l...

First of all, your DATE9. format simply tells SAS how to print the date, but doesn't influence the internal numeric value. That numeric value is either a number of days (a SAS DATE value), or a number of seconds (a DATETIME vale). Because of this, the underlying numbers are quite different.

Try running this piece of code:

data datetest;
date1 = 18993;
format date1 date11.;
date2 = 1640995200;
format date2 datetime18.;
output;
run;
proc print;

You'll see that the printed date is 1 JAN 2012, even though the underlying numbers are quite different.

I suggest you print off a few of your date values from each table with no date formatting. The following code should print off a dozen from WORK.One:

proc print data=work.one(obs=12);
var date;
format date best15.;

and do the same from WORK.Two.

If either is in a DATETIME format (the ten digit number), that's your problem. The data won't match properly.

You can convert any DATETIME values to DATE values with the DATEPART function, as demonstrated below.

data datetest;
date1 = 18993;
format date1 date11.;
date2 = 1640995200;
format date2 datetime18.;
date3 = DATEPART(date2);
format date3 best16.;
date3f = date3;
format date3f date11.;
output;
run;
proc print;
run;

Keep us posted!
  Tom

[DasRegulator · Accepted solution]

Hi All,

With your help I figured it out.  I didn't realize that the tables were organized by a different variable that also utilized the date variable so the join referenced both dates on the unseen variable. 

Thank you so much for your guidance. 

Best,

Regulator