topic Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted in SAS Enterprise Guide

Help: SAS Proc Export exports data without headers and only numerical values are extracted

L5ive — Wed, 30 Sep 2020 11:55:23 GMT

Im trying to add a PROC EXPORT Step in my project however when i use the following code:

----------------------------------------------------------------------------------------------------

%let Export=\C:\sas temp\amahert\MAHER\EXPORT STEP\CLAIMS_TREND;

PROC EXPORT DATA=ODBCAM.Provider
OUTFILE= "&Export\CLAIMS_TREND_DATA2.xlsx" dbms=xlsx REPLACE;
;RUN;

----------------------------------------------------------------------------------------------------

the extracted file shows a corrupted error and then opens the file with no headers

however if i extracted the file in TAB, CSV, TXT there is no problem with the data

also if i extracted the file as a step in project or manual from Export Wizard also there is no problem with the file.

its been very annoying trying to solve this, Thank you.

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

ballardw — Wed, 30 Sep 2020 14:51:48 GMT

What happens if you use DBMS=Excel?

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

L5ive — Wed, 30 Sep 2020 15:11:31 GMT

shows an error as follows
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

Kurt_Bremser — Wed, 30 Sep 2020 15:19:43 GMT

What's you SAS version?

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

Tom — Wed, 30 Sep 2020 15:26:19 GMT

Do you really have that extra backslash in front of the drive letter in your filename? Why?

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

jebjur — Wed, 30 Sep 2020 15:29:33 GMT

Perhaps it would be better for you to open a track with Technical Support. Do you get any errors in the log after running the code?

For example: Error creating XLSX file -> C:\C:\users\<userid>\export_test\CLAIMS_TREND_DATA2.xlsx . It is either not an Excel spreadsheet or it
is damaged. Error code=80001030
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.

If so, the path you have listed on the %LET statement may be incorrect. Try removing the '\' before the C drive letter:

Instead of: %let Export=\C:\sas temp\amahert\MAHER\EXPORT STEP\CLAIMS_TREND;

Change to: %let Export=C:\sas temp\amahert\MAHER\EXPORT STEP\CLAIMS_TREND;

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

L5ive — Wed, 30 Sep 2020 15:29:56 GMT

my version of Sas is 9.04.01M6P111518

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

L5ive — Wed, 30 Sep 2020 15:31:54 GMT

no sorry the backlash doesn't exist in the original Code. however the error still occur

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

jebjur — Wed, 30 Sep 2020 15:37:57 GMT

Any errors in the log?

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

Kurt_Bremser — Wed, 30 Sep 2020 15:40:00 GMT

If a fairly current version creates a corrupt Excel file, this is a case for SAS technical support, as this might be a bug in something that's been production stage for quite some time.

Re: Help: SAS Proc Export exports data without headers and only numerical values are extracted

Tom — Wed, 30 Sep 2020 15:40:26 GMT

Can you get PROC EXPORT to write the dataset to a different location? If so then the issue might be with the file or folder you are trying to write into.

Can you get PROC EXPORT to write a different dataset properly? For example try saving SASHELP.CLASS. If so then there might be some strange characters in your dataset. What is your setting for the ENCODING option?

When you say "Export Wizard" are you talking about an Enterprise Guide tool? That will download the file and work on it using Enterprise Guide code instead of using SAS code to export the file.

What about just writing the data as a dataset using the XLSX engine instead of using PROC EXPORT?

libname out xlsx "&Export\CLAIMS_TREND_DATA2.xlsx";
data out.Provider;
  set ODBCAM.Provider
run;

Again if that has issues try writing a different dataset and to a different file.