Proc append not always working, when called using %include

[Stat_prevmed]

We have a series of sas files that we run using include statements.  All of the code in the files runs successfully accept proc append. We are using Proc Append to add data to SQL tables (ODBC connection).  50% of the time it works just fine,  50% of the time the log says it has added records but when you look the actual tables there are no new records. The weird thing is if you run the code on its own not using and %include is always works.  Any ideas

Here is the code:

proc append data=FU3 base=check.FU3_comp force;
run;

Here is the log:

NOTE: Appending WORK.FU3 to CHECK.FU3_comp.
NOTE: There were 26 observations read from the data set WORK.FU3.
NOTE: 26 observations added.
NOTE: The data set CHECK.FU3_comp has . observations and 19 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds

[ballardw]

It might be more helpful to show the log when you say it successfully appends but no new records actually appear when you "view" the table.

It might help to set option source2 so the log has all the code from the include file as well.

[Stat_prevmed]

The log I pasted in is what appears when it runs but does not append

Thanks

[mkeintz]

If you precede your code with

options source2;

then all the code generated by the %INCLUDE will appear on the log, which would confirm the code being processed by the sas supervisor.

[ballardw]

Since the log you pasted shows

NOTE: The data set CHECK.FU3_comp has . observations and 19 variables.
NOTE: PROCEDURE APPEND used (Total process time):

note the highlighted text. Missing for observations makes me think there is something very odd here.

[Stat_prevmed]

I believe this is because we are deleteing the data in the table before appending, so there should be no observations.

proc sort data= check.inperson_FU out = inperson3;

where visit=3 and visit_completed in (1,2,3,4,5);
by primkey;
run;
Data fu_due3;
Set check.FU_Schedule;
Keep primkey End_Window ;
where due =1 and substr(primkey,8,1) ='3' and (Complete^=. or datepart(End_Window)<=today());
run;
proc sort fu_due3;
by primkey;
run;
data checktest;
merge inperson3 (in = b) fu_due3 (in =a);
by primkey;
if a and not b;
run;
/*
proc sort data = check.anthro_child out= AC3;
by primkey;
Where  primkey ^="" and visit =3 and C1_BMI is not null and C1_Height is not null and C1_Weight is not null;
run;

proc sort data = check.anthro_adult out= AD3;
by primkey;
Where primkey ^="" and visit =3 and A1_Height is not null and A1_Weight is not null;
run;
*/
data index;
set check.anthro_child ;
Where  primkey ^="" and visit =1 and pick_index is not null ;
keep patid pick_index;
run;

Data AC;
merge check.anthro_child (In=a Where  =(primkey ^="" and visit =3 )) index ;
by patid;
if a;
keep primkey  ACprob pick_index;
array bmi [8] C1_BMI C2_BMI C3_BMI C4_BMI C5_BMI C6_BMI C7_BMI C8_BMI;
if bmi[pick_index] ='' then ACprob = 1;
if bmi[pick_index] ^='' then ACprob = 0;
run;


Data AD;
merge  check.anthro_adult(in=a Where =(primkey ^="" and substr(primkey,8,1) ='3') );
by patid;
keep primkey A1_Weight A2_Weight A3_Weight A4_Weight;
run;

Data VLog;
set check.visit_log;
by primkey;
Where  primkey ^="" and substr(primkey,8,1) ='3';
run;

data set1_3;
set check.set1;
Where primkey ^="" and substr(primkey,8,1) ='3';
if  EDANX01 ^=. and EDDEP04 ^=. and Pf1anxiety8r ^=. and Pf2anxiety5r ^=.
and Pf2depr7r ^=. then Set1prob=0;
else Set1prob = 1;
run;

/*proc sort data = check.set2 out= set2_3;
by primkey;
Where primkey ^="" and visit =3 and foodsec0 is not null and foodsec4 is not null and foodsec6 is not null and foodsec13 is not null and 
Work_Outhome is not null and Last_Dr_Visit is not null and grow_height is not null and (Voice_Change is not null or Breast_Growth is not null);
run;*/

data set3_3;
set check.set3;
Where primkey ^="" and substr(primkey,8,1) ='3';
if Shopper ^=. and wtcntrl ^=. and FNPA1 ^=. and FNPA15 ^=. and Reg_Pop ^=. and Water ^=. and Juice_100_Mth ^=. and Fries_Mth ^=. and Con_Veg_Mth ^=. then Set3prob_3=0;
else Set3prob_3 = 1;
run;

Data set4_3;
set check.set4;
Where primkey ^="" and substr(primkey,8,1) ='3';
if eat_much ^=. and Hides_Food ^=. and Clothes_NoFit ^=. and Weekend_TV ^=. and Labored_Breath ^=.
then set4prob=0;
else set4prob=1;
Run;

Data set5_3;
set check.set5;
Where primkey ^="" and substr(primkey,8,1) ='3';
if Rushed ^=. and attempted ^=. and Last_Shop ^=. and hfi20_dairy9 ^=. and hfi73_fruit18 ^=. and hfi53_veg18 ^=. and hfi136_snacks4 ^=.
and hfi162_bevgs10 ^=. and hfi191_frgaccs12 ^=. then set5prob = 0;
else set5prob = 1;
run;

Data acctrack;
set check.accelerometer_tracking;
Where primkey ^="" and substr(primkey,8,1) ='3';
if ValidDay1 ^=. and ValidDay2 ^=. and ValidDay3 ^=. and ValidDay4 ^=. then accprob = 0;
else accprob = 1;
run;

Data Med_3;
set check.Medication;
Where primkey ^="" and substr(primkey,8,1)='3';
if takingmeds ^=. then medprob = 0;
else medprob = 1;
run;

Data ddmon;
set check.ddmoney;
Where primkey ^="" and substr(primkey,8,1) ='3';
if ED_Score ^=. and K_Score ^=. then ddprob = 0;
else ddprob = 1;
run;

proc sort data = set1_3;
by primkey;
run;
proc sort data = set3_3;
by primkey;
run;
proc sort data = set4_3;
by primkey;
run;
proc sort data = set5_3;
by primkey;
run;
proc sort data = med_3;
by primkey;
run;
proc sort data = acctrack;
by primkey;
run;
proc sort data = ac;
by primkey;
run;
proc sort data = ad;
by primkey;
run;
proc sort data = vlog;
by primkey;
run;
proc sort data = ddmon;
by primkey;
run;
proc sort data = fu_due3;
by primkey;
run;

Data FU3;
merge inperson3 (in  =z keep = patid primkey visit visit_completed Date_Complete Staff_ID) fu_due3
Set1_3 (in=a)/*Set2_3 (in = b)*/ set3_3 (in = c) set4_3 (in = d) set5_3 (in = e) acctrack (in = f) AC (in=g) AD (in=h) VLog (in=i) med_3 (in=j) ddmon (in=k);
by primkey;

if not z then inperprob = 1;
if z then inperprob =0;
if not a then Set1prob=1;
if a then Set1prob =0;
if not c then Set3Prob =1;
if c then set3prob = 0;
if not d then Set4Prob =1;
if d then set4prob = 0;
if not e then Set5Prob =1;
if e then set5prob = 0;
if not f then accprob = 1;
if f then accprob = 0;
if not  g then ACProb = 1;
if  g then ACProb = 0;
if not  h then ADProb = 1;
if  h then ADProb = 0;
if not  i then VLogProb = 1;
if  i then VLogProb = 0;
if not  j then MedProb = 1;
if  j then MedProb = 0;
if not  k then DDProb = 1;
if  k then DDProb = 0;

if  visit =3 and Sum (of Set1prob, /*Set2prob3*/ Set3prob, Set4prob, Set5prob, ACprob, ADprob, VLogProb, accprob, medprob, ddprob)>=1 then Redflag = 1;
Keep Patid primkey visit visit_completed Date_Complete Staff_ID Set1prob Set3prob Set4prob Set5prob ACprob ADprob VLogProb accprob medprob ddprob RedFlag End_Window;
run;


proc sql;
  delete from check.FU3_comp;
quit;
proc append data=FU3 base=check.FU3_comp force;
run;
*proc delete data=work._all_;
*run;

[ballardw]

---

@Stat_prevmed wrote:

I believe this is because we are deleteing the data in the table before appending, so there should be no observations.

---

I missed earlier where you posted

We are using Proc Append to add data to SQL tables (ODBC connection).

So the ODBC connection likely doesn't respond with a number of records. But that raises questions about the status of the ODBC connection as a possible cause of the intermittent behavior. Perhaps something such as network traffic or work load is delaying the transfer of data across the connection. And since you don't show any of the ODBC connection details there could be some other issue related to ODBC and that data source/ target.