topic Re: Using a date lookup table with a condition when the date is formatted as a character. in SAS Procedures

Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 14:16:36 GMT

Hi There.

I've started using a new DWH at work and they've introduced lookup tabels for practically everything.

I have a large dataset that contains a number (key) that links to a date that SAS is reading as character (theyre using SQL and dont know why its not recognising it as a date)

What I want to do conditionally search the large table by date so for example.

Lookup table has two variables (date is formatted as character)

DateKey TranDate

1 | 2016-01-01

2 | 2016-01-02

3 | 2016-01-02

data set has numerous variables, one being the datekey which is formated as 6.

I want to merge them together but might add a condition such as:-

Where = (Trandate >= '01Jan2016'd);

is this possible? the only way I can do it is to read the entire dataset in then merge it with the lookup table but this obviously takes time due to the size of the table.

Any help greatly appreciated.

Re: Using a date lookup table with a condition when the date is formatted as a character.

LinusH — Tue, 30 Aug 2016 14:28:44 GMT

The preferre way of using lookup tables for filtering is by using SQL inner joins. The is the usual technique when dealing with star schemas, and similar.

But since your date in the date lookup table (dimension?) is char, you can't use a SAS date constant, since that is considered as numeric.

You could probably use the same date char format as your lookup table, even if it's not ideal. Is the data stored in SAS data sets?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 14:54:27 GMT

@LinusH wrote:

The preferre way of using lookup tables for filtering is by using SQL inner joins. The is the usual technique when dealing with star schemas, and similar.

But since your date in the date lookup table (dimension?) is char, you can't use a SAS date constant, since that is considered as numeric.

You could probably use the same date char format as your lookup table, even if it's not ideal. Is the data stored in SAS data sets?

I do have a piece of code that will change the character dates to an actual date format, as the lookup table only consists of about 10000 observations I dont think this would take too long.

If I did this and the date was in mmddyy10. could I then use the SQL inner join?

If so would you able to provide an example?

Really apprecate the help.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Astounding — Tue, 30 Aug 2016 15:09:01 GMT

A few notes ...

Since your character date is in Y-M-D form, you can compare to it directly:

where = (TranDate >= '2016-01-01')

If you want to compare these character values to the numeric values in a larger data set, that can be done as well. But it's not clear what that other data set contains. Does it contain numeric dates, numeric datetimes, something else entirely? What would it contain that lets it be joined to your lookup table in any way? DATEKEY?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 15:19:20 GMT

@Astounding wrote:

A few notes ...

Since your character date is in Y-M-D form, you can compare to it directly:

where = (TranDate >= '2016-01-01')

If you want to compare these character values to the numeric values in a larger data set, that can be done as well. But it's not clear what that other data set contains. Does it contain numeric dates, numeric datetimes, something else entirely? What would it contain that lets it be joined to your lookup table in any way? DATEKEY?

Apologies if didnt make it clear.

Ok so both tables have a variable called DateKey which is a number and the lookupdate contains the date ( i've just converted the dates in the lookup to mmddyy10. so the character thing isnt an issue now.

What I would like to do is join the tabkes together by the variable datekey but only bring back obersvations from the non lookuptable where date >= 01Jan16 etc.

The non lookup table has millions of observations so i need to make sure that in the step im joining the tables together I am able to define some conditional criteria.

That make sense?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Astounding — Tue, 30 Aug 2016 15:25:58 GMT

Here are the pieces that I'm not sure about.

When you say you converted the lookup dates to mmddyy10 format, what does that mean? Do you have an actual SAS date value (numeric) stored there, with a format applied for printing purposes?

When you have a DATE variable in the nonlookup table, what does it contain? A date value on the SAS date scale? A datetime value? A character string?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 15:40:48 GMT

Hi. Heres the tables i have

Lookup Table... Datekey is a numerical format, date is now in dateformat

DateKey Date

1 01/01/2016

2 02/01/2016

3 03/01/2016

4 04/01/2016

Transaction Table Contains multiple variables however also contains the DateKey

DateKey Var2 Var3 Var4

I need to join the two tables by datekey but i'd like to include the statment where the date in the joined table is = 03/01/2016. (or any other condition of my choosing)

I'm not 100% sure if this is possible

Help continues to be appreciated.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Shmuel — Tue, 30 Aug 2016 15:45:52 GMT

Do I understand right:

if TRANDATE was in a SAS date (numeric) format

then you will use : Where = (Trandate >= '01Jan2016'd);

As TRANDATE is a character variable you can use:

where = (input(trandate,yymmdd10.) >= '01JAN2016'd);

Re: Using a date lookup table with a condition when the date is formatted as a character.

Astounding — Tue, 30 Aug 2016 15:52:52 GMT

I must be having a slow day. Here's the part I still don't understand: "date is now in dateformat"

What is actually contained in the field DATE? A character string? A numeric value on the SAS date scale? A numeric value on some other scale that your data warehouse uses? A numeric value that corresponds to a date-time rather than a date?

Whatever the answer, if you want to compare with date fields in the non-lookup table(s), the same questions will apply to those date fields.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 19:11:28 GMT

Sorry it's my terminology.

So it's a sas date scale, a number given a format to show a proper date. This date is only in the lookup table. However both tables have a datekey this key is what the two tables need to be merged by however i only want the tables to merge data where the date is after a certain date.

As the actual date is only in the lookup table I'm not sure if this is possible.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Astounding — Tue, 30 Aug 2016 19:29:46 GMT

OK, I think I'm starting to understand the issues here. You can't use a standalone WHERE statement, since the date field appears in only one data set. This program would require the date to be in both data sets:

data want;

merge huge_table (in=in1) lookup_table (in=in2);

by datekey;

if in1 and in2;

where TranDate >= '01Mar2016'd;

run;

That would generate an error, because TranDate doesn't appear in HUGE_TABLE.

Instead, use the WHERE clause as a data set modifier:

data want;

merge huge_table (in=in1) lookup_table (in=in2 where=(TranDate >= '01Mar2016'd));

by datekey;

if in1 and in2;

run;

All of this assumes you're actually using MERGE to combine the data ... if you are using SQL it can be done but would look a bit different.

Are we finally headed in the right direction?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Shmuel — Tue, 30 Aug 2016 19:30:42 GMT

try:

proc sql;

create table want as select

a.*, b.* /* change to desired variables */

from lookup_table(where=(date='03JAN2016'd)) as a

left join transaction_table as b

on (a.date_key = b.datekey)

; quit;

Is that what you want ? if not addapt it to your needs.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Tue, 30 Aug 2016 19:32:23 GMT

I think we are heading in the right direction my friend.

I'm not at work at the moment but ill give them a shot tomorrow.

Will report back.

Thanks again

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Wed, 31 Aug 2016 06:54:32 GMT

Ok had a pop at this this morning

proc sql;
create table want as select
a.DateKey, b.Date /* change to desired variables */
from DateKey(where=(date>'03JAN2016'd)) as a
left join Payments as b
on (a.Datekey = b.Datekey)
; quit;

but got this error merssage

898 proc sql;
899 create table want as select
900 a.DateKey, b.Date /* change to desired variables */
901 from DateKey(where=(date>'03JAN2016'd)) as a
902 left join Payments as b
903 on (a.Datekey = b.Datekey)
904 ;
ERROR: Column Date could not be found in the table/view identified with the correlation name B.
904! quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Wed, 31 Aug 2016 07:02:25 GMT

Ok changed it to this

proc sql;
create table want as select
a.DateKey,Date, b.DateKey, TransactionAmount/* change to desired variables */
from DateKey(where=(date>'28Aug2016'd)) as a
left join Payments as b
on (a.Datekey = b.Datekey)
; quit;

and heres the log.

953 proc sql;
954 create table want as select
955 a.DateKey,Date, b.DateKey, TransactionAmount/* change to desired variables */
956 from DateKey(where=(date>'28Aug2016'd)) as a
957 left join Payments as b
958 on (a.Datekey = b.Datekey)
959 ;
WARNING: Variable DateKey already exists on file WORK.WANT.
NOTE: Table WORK.WANT created, with 223 rows and 3 columns.

959! quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

However while it has merged the transaction amount variable where the dates are > 28/08/2016 it has still brought back the other variables from the lookup table (i.e where the date is < 28/08/2016 its juts put the transaction amount as blank.

All i want to see is where the date is > 28/08/2016, think we're nearly there though

Re: Using a date lookup table with a condition when the date is formatted as a character.

Shmuel — Wed, 31 Aug 2016 08:47:09 GMT

I woul like to clarify some points:

- is the look_up table named work.datekey ?

- is the date you want to check is on that work.datekey table and is named DATE ?

- is the transaction table named as work.payments ?

Now back to my oroginal proposed code - addapted to the above:

proc sql;

create table want as select

a.*, b.* /* change to selet desired variables, the asterisk (*) is to select all variables */

from work.DateKey (where=(date='03JAN2016'd)) as a

left join work.payments as b

on (a.date_key = b.datekey)

; quit;

In case it does not meet your expectations please clarify where and why ?

The error you got:

ERROR: Column Date could not be found in the table/view identified with the correlation name B.

is because the DATE variable is not in work.payments table (as B) but in work.DateKey table (as A)

Don't hesitate to ask more if need.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Wed, 31 Aug 2016 09:11:48 GMT

Good Morning.

Re your comments:-

- is the look_up table named work.datekey ? Yes

- is the date you want to check is on that work.datekey table and is named DATE ? Yes

- is the transaction table named as work.payments ? Ive amended the code slightly, the source table is called =-DWH.vwFactSalaryTransactions

The last one I ran was this:-

proc sql;
create table Payments as select
a.DateKey, Date, b.DateKey, TransactionAmount, AccountKey, TimeKey, TransactionCodeKey, NarrativeKey, TransactionAmount
from DateKey(where=(date>'28Aug2016'd)) as a
left join DWH.vwFactSalaryTransactions as b
on (a.Datekey = b.Datekey)
; quit;

Ive then amended it to your most recent code

proc sql;
create table Payments as select
a.*, b.*
from work.DateKey(where=(date>'28Aug2016'd)) as a
left join DWH.vwFactSalaryTransactions as b
on (a.Date_key = b.Datekey)
; quit;

It seems to not like "Date_Key"

45 proc sql;
46 create table Payments as select
47 a.*, b.*
48 from work.DateKey(where=(date>'28Aug2016'd)) as a
49 left join DWH.vwFactSalaryTransactions as b
50 on (a.Date_key = b.Datekey)
51 ;
ERROR: Column Date_key could not be found in the table/view identified with the correlation name A.
ERROR: Column Date_key could not be found in the table/view identified with the correlation name A.
ERROR: Expression using equals (=) has components that are of different data types.
51 ! quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.28 seconds
cpu time 0.03 seconds

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Wed, 31 Aug 2016 09:17:04 GMT

So it seems to be merging the data, when I say that, there are exactly 100 observations from the table vwFactSalaryTransactions where the date is > 28/08/2016.

However it is still returning all the dates from the lookup table that are > 28/08/2016 but leaving out the data from vwFactSalaryTransactions.

What i want to do is not retain that data whatsover so the output would be 100 observations

That make sense?

Re: Using a date lookup table with a condition when the date is formatted as a character.

Shmuel — Wed, 31 Aug 2016 09:37:21 GMT

I understand that the key variable on both tables is DateKey.

The error you got tells that Date_key could not be found in work.DateKey (A). Delte the hyphen .

After running without syntax errors, if you still get observations with date =< '28Aug2016'd

then please attach lines from output that you have not expected and the fuul log of your run.

Re: Using a date lookup table with a condition when the date is formatted as a character.

Stretlow — Wed, 31 Aug 2016 09:46:52 GMT

@Shmuel wrote:

I understand that the key variable on both tables is DateKey.

The error you got tells that Date_key could not be found in work.DateKey (A). Delte the hyphen .

After running without syntax errors, if you still get observations with date =< '28Aug2016'd

then please attach lines from output that you have not expected and the fuul log of your run.

Hi.

I've attached the output. Ive written a second datastep to just say

data remove;
set Payments(Where = (Accountkey ne .));

run;

to remove the records however im sure we should be able to do it in the proq sql statement.

Again thanks for the help.