topic Re: Proc Export procedure stopped working after installing STAT in New SAS User

Proc Export procedure stopped working after installing STAT

mfp — Wed, 30 Oct 2019 20:11:46 GMT

I have a simple Proc Export that is looped to create a multi-tabbed spreadsheet. Its worked for years. I recently reinstalled SAS after purchasing SAS/STAT. Now, there is only the last database in a tab. The log shows no errors and that all sheets were created. It looks like each iteration overwrites the previous tab. Worked for years. Any clue?

%MACRO DOIT(OUTDATA,TAB,OUTEXL);

PROC EXPORT DATA=COMPANY.&RESPONDENT._&SEGMENT._&STAGE._&OUTDATA
            OUTFILE= "&ROOT\&RESPONDENT._&SEGMENT._&STAGE._Margin Analysis_&OUTEXL"
            DBMS=XLSX REPLACE;
     SHEET="&TAB";
RUN;

%MEND DOIT;

%DOIT(conmargins,Margins By CONNUM, &sysdate);
%DOIT(hmwtav,Weighted Average NVs, &sysdate);
%DOIT(tranmarg,A to T Margin, &sysdate);
%DOIT(avgmarg,Standard Margin, &sysdate);
%DOIT(concord,Concordance, &sysdate);
%DOIT(unique,Simple Concordance, &sysdate);
%DOIT(unique2,Unique Concordance, &sysdate);
%DOIT(matches,HM Sales matched to US, &sysdate);

Re: Proc Export procedure stopped working after installing STAT

Reeza — Wed, 30 Oct 2019 20:38:20 GMT

Did you update versions of SAS as well? I'm guessing you did and the Excel export has changed. What version of SAS are you on now?
You may need to remove the REPLACE option from the export now. You can also try ODS EXCEL libname XLSX and see if you get the same behaviour.

I'm a big fan of ODS EXCEL but it does depend on your requirements and data size. It doesn't work well with large data sets.

Re: Proc Export procedure stopped working after installing STAT

mfp — Wed, 30 Oct 2019 21:19:50 GMT

Thanks, Reeza. I did not change versions (9.3 before and after). It was a fresh install, so there may have been hotfixes that got installed that were not there beforehand. Was there a hotfix that related to proc export?

Re: Proc Export procedure stopped working after installing STAT

mfp — Wed, 30 Oct 2019 21:23:34 GMT

I ran it without the REPLACE and got this error message. The spreadsheet now has just the 1st dataset rather than just the last one.

NOTE: Export cancelled.  Output file C:\SAS Data\Quartz\India\Antique\ANTIQUE_INV_BASE1_Margin Analysis_30OCT19.xlsx already exists. Specify REPLACE option
      to overwrite it.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Proc Export procedure stopped working after installing STAT

Tom — Wed, 30 Oct 2019 21:32:50 GMT

Did you really go to the trouble of re-installing SAS and install an 8 year old version?

Re: Proc Export procedure stopped working after installing STAT

Reeza — Wed, 30 Oct 2019 22:35:28 GMT

Then, I would recommend talking to SAS tech support. Another possible issue is that with newer versions especially in 9.3 SAS switched to 64 bit and if Excel is 32 bit you may have issues and there's all sorts of things. Given a fresh install I was assuming 9.4.

Ultimately the question is though, do you want to find out why this happens and fix it, or do you want to fix the code to work and move on for now?