CODEWITHSUNDEEP
error-handlingsas
IceCreamToucan
IceCreamToucan

Reputation: 28685

check whether proc append was successful

I have some code which appends yesterday's data to [large dataset], using proc append. After doing so it changes the value of the variable "latest_date" in another dataset to yesterday's date, thus showing the maximum date value in [large dataset] without a time-consuming data step or proc sql.

How can I check, within the same program in which proc append is used, whether proc append was successful (no errors)? My goal is to change the "latest_date" variable in this secondary dataset only if the append is successful.

Upvotes: 1

Views: 662

Answers (3)

momo1644
momo1644

Reputation: 1804

You can do this by counting how many records are in the table pre and post appending. This would work with any sas table or database. The best practice is to always have control table for your process to log run time and number of records read.

Code:

/*Create input data*/
data work.t1;
input row ;
datalines;
1
2
;;
run;
data work.t2;
input row ;
datalines;
3
;;
run;

/*Create Control table, Run this bit only once, otherwise you delete the table everytime*/
data work.cntrl;
length load_dt 8. source 8. delta 8. total 8. ;
format load_dt datetime21.;
run;
proc sql; delete * from work.cntrl; quit;

/*Count Records before append*/
proc sql noprint ; select count(*) into: count_t1 from work.t1; quit;
proc sql noprint; select count(*) into: count_t2 from work.t2; quit;

/*Append data*/
proc append base=work.t1 data=work.t2 ; run;

/*Count Records after append*/
proc sql noprint ; select count(*) into: count_final from work.t1; quit;

/*Insert counts and timestampe into the Control Table*/
proc sql noprint; insert into work.cntrl 
/*values(input(datetime(),datetime21.), input(&count_t1.,8.) , input(&count_t2.,8.) , input(&count_final.,8.)) ; */
values(%sysfunc(datetime()), &count_t1. , &count_t2., &count_final.) ; 
quit;

Output: Control table is updated Control Table

Upvotes: 0

IceCreamToucan
IceCreamToucan

Reputation: 28685

I'm using the %get_table_size macro, which I found here. My steps are

  1. run %get_table_size(large_table, size_preappend)
  2. Create dataset called to_append
  3. run %get_table_size(to_append, append_size)
  4. run proc append
  5. run %get_table_size(large_table, size_postappend)
  6. Check if &size_postappend = &size_preappend + &append_size

Using &syscc isn't exactly what I wanted, because it doesn't check specifically for an error in proc append. It could be thrown off by earlier errors.

Upvotes: 0

DomPazz
DomPazz

Reputation: 12465

Try the automatic macro variable &SYSCC.

data test;
do i=1 to 10;
    output;
end;
run;

data t1;
i=11;
run;

data t2;
XXX=12;
run;

proc append base=test data=t1;
run;

%put &syscc;

proc append base=test data=t2;
run;

%put &syscc;

Upvotes: 3

Related Questions