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

[Shmuel]

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.

[Stretlow]

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

[Stretlow]

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?

[Shmuel]

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.

[Stretlow]

---

@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.

[Shmuel · Accepted solution]

To  filter records where accountkey is missing is easy, but I need to know which table holds this variable.

Let's say its origin is the B table, then the full code will be:

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

if accountkey is in the lookup table (A) then:

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

[Stretlow]

Hi.

The blank data only appears after the merge.

Stret

[Shmuel]

It doesn't make sense to me. Using SQL the JOIN (merge) is cartesian.

It means that ACCOUNTKEY with missing value got this value from the origin table.

Which table  contains the accountkey variable ?

is this variable on both tables ?

are you sure there are no observations with accountkey missing ?

is accountkey numeric or characte defined ?

if character then filter it by  - accountkey ne ' '

[Astounding]

I would have to see the log to see if it makes sense.  Unless the actual program is more complex than what you have been describing, the log should be quite short.

[Stretlow]

If you take a look at the output xlsx sheet i attached, thats the output from the merge.

Al ive done is added a data step to remove the blank entries, I could have used anyone of the variables in there.

[Shmuel]

You wrote that the actual program is more complex, so in order to be able to help you more 

I need the whole log. May be the missing values of accountkey derive from a previous step. 

[Astounding]

We're talking apples and oranges here.  The spreadsheet shows the output data set.  I need to see the log, to review the programming statements that ran and the notes that SAS generated about them.

[Stretlow]

Hi. Sorry ive taken so long to post back, i had to take some unexpected time from work.

Anyhows with a little tinkering i've now got this to work.

Thanks very much for all your help

Stret