Here is the log and then followed below by the code. I'm working with very large datasets (30GB each) and just trying to merge them based on unique identifiers so I figured that's why it takes a long time to run.
Log:
38 libname proclm 'J:\SLAP Demographics\Proc\Claim';
NOTE: Libref PROCLM was successfully assigned as follows:
Engine: V9
Physical Name: J:\SLAP Demographics\Proc\Claim
39 *getting the claim data for the procedures so we have the dates;
40 %macro prmerge(lib1=, lib2=, data1=, data2=);
41 %do i = 15 %to 18;
42
43 proc sql;
44 create table &lib2..&data1._&i._&data2. as
45 select *, (proc_&i._&data2._ids.clm_trans_id = &data1._&i..clm_trans_id)
45 ! as indic,
46 (proc_&i._&data2._ids.clm_trans_id ~='') as FROMproc_ids,
47 (&data1._&i..clm_trans_id ~='') as FROM&data1.
48 from proc.proc_&i._&data2._ids full join &lib1._&i..&data1._&i. on
48 ! proc_&i._&data2._ids.clm_trans_id=&data1._&i..clm_trans_id;
49 quit;
50
51 data &lib2..&data1._&i._&data2.;
52 set &lib2..&data1._&i._&data2.;
53 if FROMproc_ids = 1 and FROM&data1. = 1;
54 run;
55
56 %end;
57 %mend prmerge;
58 %prmerge(lib1=diag, lib2=proclm, data1=claim, data2=out);
WARNING: Variable clm_trans_id already exists on file PROCLM.CLAIM_15_OUT.
NOTE: Table PROCLM.CLAIM_15_OUT created, with 20821258 rows and 105 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 2:08:40.25
cpu time 6:07.13
NOTE: There were 20821258 observations read from the data set
PROCLM.CLAIM_15_OUT.
NOTE: The data set PROCLM.CLAIM_15_OUT has 3551 observations and 105
variables.
NOTE: DATA statement used (Total process time):
real time 21:16.96
cpu time 40.84 seconds
WARNING: Variable clm_trans_id already exists on file PROCLM.CLAIM_16_OUT.
ERROR: Insufficient space in file WORK.'SASTMP-000000012'n.UTILITY.
ERROR: File WORK.'SASTMP-000000012'n.UTILITY is damaged. I/O processing did
not complete.
NOTE: Error was encountered during utility-file processing. You may be able
to execute the SQL statement successfully if you allocate more space to
the WORK library.
ERROR: There is not enough WORK disk space to store the results of an
internal sorting phase.
ERROR: An error has occurred.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 6:56:52.95
cpu time 4:40.64
Code:
libname proclm 'J:\SLAP Demographics\Proc\Claim';
*getting the claim data for the procedures so we have the dates;
%macro prmerge(lib1=, lib2=, data1=, data2=);
%do i = 15 %to 18;
proc sql;
create table &lib2..&data1._&i._&data2. as
select *, (proc_&i._&data2._ids.clm_trans_id = &data1._&i..clm_trans_id) as indic,
(proc_&i._&data2._ids.clm_trans_id ~='') as FROMproc_ids,
(&data1._&i..clm_trans_id ~='') as FROM&data1.
from proc.proc_&i._&data2._ids full join &lib1._&i..&data1._&i. on proc_&i._&data2._ids.clm_trans_id=&data1._&i..clm_trans_id;
quit;
data &lib2..&data1._&i._&data2.;
set &lib2..&data1._&i._&data2.;
if FROMproc_ids = 1 and FROM&data1. = 1;
run;
%end;
%mend prmerge;
%prmerge(lib1=diag, lib2=proclm, data1=claim, data2=out);
... View more