BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Garnet | Level 18

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
Obsidian | Level 7

@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
Garnet | Level 18

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
Obsidian | Level 7

Hi.

 

The blank data only appears after the merge.

 

Stret

Shmuel
Garnet | Level 18

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
PROC Star

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
Obsidian | Level 7

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
Garnet | Level 18

 

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
PROC Star

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 2477 views
  • 0 likes
  • 4 in conversation