03-15-2016 11:14 AM
I am using 9.4
The 9.3 hot fix says to
"To circumvent this problem, increase the REGION for the JOB."
How do I do that?
03-15-2016 11:42 AM
03-15-2016 12:06 PM
MPRINT(ED_AGGREGATION): proc sql; MPRINT(ED_AGGREGATION): create table tmp1 as select distinct cust_nbr, node_grp_nbr as ed_aggr_nbr from sasdata.prcg_cust_grping_acct where "15MAR16"d between grp_start_dt and grp_end_dt and mbr_action_cd = 'I' order by 1 ; NOTE: Table WORK.TMP1 created, with 331227 rows and 2 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.96 seconds cpu time 1.01 seconds MPRINT(ED_AGGREGATION): proc sql; MPRINT(ED_AGGREGATION): connect to teradata (user=muted password=muted tdpid='edwapps.prod.muted.com'); MPRINT(ED_AGGREGATION): create table tmp2 as select * from connection to teradata ( select distinct cust_nbr, ed_aggr_nbr from ui_ish_prod_db.cust_earned_disc where ed_aggr_nbr > 0 and date '2016-03-15' between disc_st_dt and disc_end_dt order by 1 ); NOTE: Table WORK.TMP2 created, with 968499 rows and 2 columns. NOTE: PROCEDURE SQL used (Total process time): real time 23.91 seconds cpu time 3.73 seconds MPRINT(ED_AGGREGATION): data ishdata1.ed_aggregation(index=(cust_nbr ed_aggr_nbr)); MPRINT(ED_AGGREGATION): merge tmp1(in=ok1) tmp2(in=ok2); MPRINT(ED_AGGREGATION): by cust_nbr; MPRINT(ED_AGGREGATION): if ok2 then ed_type='L'; MPRINT(ED_AGGREGATION): else if ok1 then ed_type='X'; ERROR: Insufficient space in file ISHDATA1.ED_AGGREGATION.DATA. ERROR: File ISHDATA1.ED_AGGREGATION.DATA is damaged. I/O processing did not complete. NOTE: The SAS System stopped processing this step because of errors. NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set. NOTE: There were 715 observations read from the data set WORK.TMP1. NOTE: There were 1918 observations read from the data set WORK.TMP2. WARNING: The data set ISHDATA1.ED_AGGREGATION may be incomplete. When this step was stopped there were 2624 observations and 3 variables. ERROR: Data set ISHDATA1.ED_AGGREGATION was not replaced because ERROR: File ISHDATA1.ED_AGGREGATION.DATA is damaged. NOTE: DATA statement used (Total process time): real time 0.15 seconds cpu time 0.03 seconds
03-15-2016 12:16 PM
Have you checked, firstly that you have disc space available after the first two sql steps, and secondly that your merge isn't merging many records to many records. I don;t have your data so can't tell, but if you merge just by cust_nbr there is a risk you could end up with 331227 * 968499 records which would be a lot of data and might fill up your disc space.
03-15-2016 01:11 PM - edited 03-15-2016 01:14 PM
I reran it and of course this time it worked.
I added option=fullstimer, so below you can see the memory usage on "a good run"
No idea of the memory on a bad run yet.
NOTE: There were 331244 observations read from the data set WORK.TMP1. NOTE: There were 968499 observations read from the data set WORK.TMP2. NOTE: The data set ISHDATA1.ED_AGGREGATION has 1296676 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 4.06 seconds user cpu time 7.37 seconds system cpu time 1.57 seconds memory 80953.56k OS Memory 93416.00k Timestamp 03/15/2016 11:26:36 AM Step Count 137 Switch Count 0 Page Faults 3 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 11930 Involuntary Context Switches 1029 Block Input Operations 3 Block Output Operations 126
03-15-2016 04:49 PM - edited 03-16-2016 03:58 AM
A data step merge needs next to no memory, so memory is not an issue here, as the error message also clearly states. You ran out of disk space in your ISHDATA1 location while writing the result file.
Check if your ISHDATA1 location/filesystem is used by other users who also use lots of space.
Monitor disk usage in your ISHDATA1 location/filesystem (df -k filesystem from the commandline) before and while your step runs, and compare that with the size of your resulting dataset when the step completed successfully.
Edit: ISHDATA1 instead of WORK
03-15-2016 12:14 PM
03-15-2016 12:16 PM - edited 03-15-2016 12:16 PM
SunOS mktpricing 5.10 Generic_150400-20 sun4v sparc sun4v
03-15-2016 12:14 PM
In general, you set MEMSIZE. For example, using default MEMSIZE:
sas prog1 -memsize 2G
There may be limits you face on how much memory you are permitted to use.
03-15-2016 12:25 PM
03-17-2016 03:29 AM
You can either mark the thread as solved, or mark one of the answers as the solution (which also has the benefit of rewarding the one that gave you the crucial hint). As the threadstarter and -owner you have to do this.
03-17-2016 10:02 AM
how? I have tried... Send me a screenshot picture perhaps of where the SOLVED button is located... My old eyes can't seem to find it!
Need further help from the community? Please ask a new question.