topic Re: export sas data file into excell in SAS Programming

export sas data file into excell

zhuxiaoyan1 — Thu, 08 Jun 2017 16:02:24 GMT

I use this code to export my three sas data files into an excell file with three sheets:

proc export data=EPI_PCT dbms=xlsx

outfile='\\nasnpmmds\EBI1\mmds\AHCPII\Pricing\Threshold_Updating\Threshold Update-Danielle\8-CABG / RB8' replace;

sheet="EPI_PCT";

run;

proc export data=PAP_PCT dbms=xlsx

outfile='\\nasnpmmds\EBI1\mmds\AHCPII\Pricing\Threshold_Updating\Threshold Update-Danielle\8-CABG / RB8' replace;

sheet="PAP5_PCT";

run;

proc export data=Gain_Risk dbms=xlsx

outfile='\\nasnpmmds\EBI1\mmds\AHCPII\Pricing\Threshold_Updating\Threshold Update-Danielle\8-CABG / RB8' replace;

sheet="Gain_Risk";

run;

but it give me error message:

The SAS/ACCESS Interface to PC Files is not installed. Please install this module in order to IMPORT/EXPORT to these file types.

even I installed sas pc file server. My SAS version is 9.3 TS1M2, Ms 32 bits, Windows 64 bits. I've installed pcfilesrv_931401_win_EN_web_1. Have I installed a wrong pcfile server?

Re: export sas data file into excell

ballardw — Thu, 08 Jun 2017 16:18:50 GMT

With PC File server you use a libname statement to link to the desired XLSX file, or create a new one, and use data step or other procedures to write to the library. Then clear the libname so SAS no longer locks the file.

Some thing like

libname myexc PCFILES path="\\nasnpmmds\EBI1\mmds\AHCPII\Pricing\Threshold_Updating\Threshold Update-Danielle\8-CABG\RB8.xlsx";

data myexc.EPI_PCT ;
   set EPI_PCT ;
run;

data myexc.PAP_PCT ;
   set PAP_PCT ;
run;
data myexc.Gain_Risk  ;
   set Gain_Risk  ;
run;

libname myexc clear;

I am not exactly sure of the path and file name as your use of mixed / and \ in the path looks odd. I also recommend no spaces.

Re: export sas data file into excell

Reeza — Thu, 08 Jun 2017 17:18:07 GMT

XLSX will have a limit of one sheet to a file in SAS 9.3 TS1M3 so you can't do multiple sheets.

Upgrades are free, but since you're on a server that's not a straightforward process and you likely have to go through IT and can't control that.

Another option would be

1) Rich Poor Proc Export Macro - user written, a lot functionality as desired.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

2) Tagsets.Excelxp - Well documented, lots of options but not a native XLSX file, it creates an XML file instead.

Re: export sas data file into excell

zhuxiaoyan1 — Thu, 08 Jun 2017 18:32:07 GMT

I tried tagsets.ExcelXp and created a file, but my computer could not open the file. I also tried Export Macro, it gave me error massage: Undetermined I/O failure.

Re: export sas data file into excell

Reeza — Thu, 08 Jun 2017 18:42:57 GMT

@zhuxiaoyan1 wrote:
I tried tagsets.ExcelXp and created a file, but my computer could not open the file. I also tried Export Macro, it gave me error massage: Undetermined I/O failure.

I can't do anything with this information. If you need further assistance you're going to have to provide more details.

Other things you should try:

1. Can you 'manually' export the file. If in BASE right click dataset and Export.

2. Check your licenses, use PROC SETINIT and/or PROC PRODUCT_STATUS to verify what you have installed.

3. Export as CSV and convert to XLSX manually.

FYI - SAS 9.3 is getting close to a decade old at this point. Expecting it to work with Excel 2013 isn't reasonable.