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

[L5ive]

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.

[ballardw]

What happens if you use DBMS=Excel?

[L5ive]

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

[Kurt_Bremser]

What's you SAS version?

[L5ive]

my version of Sas is 9.04.01M6P111518

[Kurt_Bremser]

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.

[Tom]

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

[jebjur]

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;

[L5ive]

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

[jebjur]

Any errors in the log?

[Tom · Accepted solution]

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.