INTCK Error

[Dogo23]

Hi all,

I'm trying to only grab data from table A where the date field is within 14 days of the send_date field in table B.

I'm getting the following error after running the code below:

ERROR: Insufficient space in file WORK.'SASTMP-000000168'n.UTILITY.
ERROR: File WORK.'SASTMP-000000168'n.UTILITY is damaged. I/O processing did not complete.

proc sql;
create table t1 as 
select 
distinct a.campaign_history_id,
account_number,
send_date,
bookings,
revenue
revenue_data a
left join
metadata_match b
on a.campaign_history_id = b.campaign_history_id
and intck ('day', a.date, b.send_date) <= 14
group by 1,2;
quit;

The date format for table A is:dtdate9.

For table B it is:mmddyy8.

I assumed that if they were both in a data format, the function would work even if the formats varied from each other.

Any advice is appreciated!

[Astounding]

The field a.date is not a date.  If it's using the dtdate format, it's a datetime.   As a result, INTCK computes all differences as being less than 14, and you don't have room to store all that.

A better comparison might be:

and b.send_date - datepart(a.date) <= 14

Or it might be the reverse:

and datepart(a.date) - b.send_date <= 14

It's a little difficult to tell without looking at a little of the data.

[ChrisBrooks]

I don't think this has anything to do with the formats used or the intck function. It looks like you've simply run out of work space - my advise would be:

1. To delete any files in the work area which are not needed for this step or subsequent steps; and if that doesn't do the trick then

2. If you're running on a server see if you can get the system admins to increase the amount of work space available to you

[Tom]

The date format for table A is:dtdate9.

For table B it is:mmddyy8.

You are comparing apples to oranges.  (or perhaps oranges to kumquats).

If the values from table A when displayed with the DTDATE9 format don't all look like they are from early january 1960 then they are actually DATETIME values and not DATE values.  DATETIME values are the number of seconds and DATE values are the number of days.  You can use the DATEPART() function to convert the seconds into days.

and intck ('day', datepart(a.date), b.send_date) <= 14

Edit by KB: changed seconds to days (see bold typeface)

[Kurt_Bremser]

See Maxim 10. The way a proc sql join works, all data (all needed columns, all records from all input datasets) is thrown into one big bucket (the utility file), from which SAS then picks those that meet the join condition. In a lot of cases, this performs way less (can be two orders of magnitude and more) than other methods of joining.

Depending on the relationship of your tables with regard to campaign_history_id, a merge in a data step (with the necessary sorts preceding it) might be the better approach.

PS the issue found by @Tom needs to be cleared first, of course.