Yesterday, my programs ran fine. I've been running them thousands of times for months. Today, after upgrading Office from 2010 to 2019, the most bizarre behavior has started. It seems to be in the PROC APPEND.
Sometimes it will run once, then, when I run it again, the SAS session just closes. The program just blows up and disappears. It is bizarre. Here is the log on the first run. The Excel file does not exist so it creates it. Those two lines that say INFO appear to be new. I have not noticed these before.
NOTE: Appending WORK.MFPRESULTS to WRKBK.raw.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
NOTE: There were 1 observations read from the data set WORK.MFPRESULTS.
NOTE: The data set WRKBK.raw has 1 observations and 32 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.13 seconds
cpu time 0.09 seconds
MPRINT(G200_COST_SENSITIVITY): LIBNAME WrkBk CLEAR;
NOTE: Libref WRKBK has been deassigned.
9207
9208 data _null_;
9209 call sound(500,200);
9210 call sound(2000,400);
9211 call sound(500,200);
9212 run;
Here are the subsequent runs (Ignore the warnings on the variable lengths):
NOTE: Appending WORK.MFPRESULTS to WRKBK.raw.
WARNING: Variable mixed has different lengths on BASE and DATA files (BASE 255 DATA 3).
WARNING: Variable alt has different lengths on BASE and DATA files (BASE 255 DATA 3).
WARNING: Variable us1 has different lengths on BASE and DATA files (BASE 255 DATA 20).
WARNING: Variable hm1 has different lengths on BASE and DATA files (BASE 255 DATA 20).
WARNING: Variable cp1 has different lengths on BASE and DATA files (BASE 255 DATA 20).
WARNING: Variable VARNAME has different lengths on BASE and DATA files (BASE 255 DATA 7).
WARNING: Variable today has different lengths on BASE and DATA files (BASE 255 DATA 18).
NOTE: There were 1 observations read from the data set WORK.MFPRESULTS.
NOTE: 1 observations added.
NOTE: The data set WRKBK.raw has . observations and 32 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.20 seconds
cpu time 0.09 seconds
MPRINT(G200_COST_SENSITIVITY): LIBNAME WrkBk CLEAR;
NOTE: Libref WRKBK has been deassigned.
9207
9208 data _null_;
9209 call sound(500,200);
9210 call sound(2000,400);
9211 call sound(500,200);
9212 run;
NOTE: DATA statement used (Total process time):
real time 1.64 seconds
cpu time 0.01 seconds
When it blows up, it actually writes the line to the Excel spreadsheet. The log stops after printing the CPU time. it pauses for about 20 seconds. then the window closes: poof!
So the log never generates the clearing the LIBNAME step.
You say "Excel". Which engine do you use in the LIBNAME statement?
Kurt:
This is the statement. It is the last statement in a called Macro file. When the Macro ends, the program that called it ends. Its worked thousands of times before switching to 2019:
LIBNAME WrkBk EXCEL "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._additional analysis.xlsx" SCAN_TEXT=NO;
PROC APPEND force BASE=WrkBk.raw
DATA=mfpresults;
RUN;
LIBNAME WrkBk CLEAR;
%MEND G200_cost_sensitivity;
So you are dependent on the Microsoft-supplied engine, which has changed with the "upgrade".
Bring this to the attention of SAS technical support, maybe they can do something about it.
Is your SAS current (at least 9.4M6)? The issue may have been fixed in a recent maintenance level.
TBH, I would never do such a thing. I'd keep my complete data in SAS and only do exports to Excel in a final step, dataset-wise, for the poor people who need data in that format.
Yes, 9.4 TS1M6. I will call SAS Tech support. Thanks
Kurt, FYI, the PROC Export in a prior step works just as it did before. Its only the PROC APPEND that is the issue.
It has to do with the "update" access mode, which is usually not available with such file formats (APPEND is not possible with the XLSX engine, for instance).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: