Hello,
When I try to export a SAS data set to dbms=excel .xls format, like this (label of variables are written in the first row),
txt1_label | num1_label | txt2_label | num2_label |
A | 1 | B | 2 |
C | 3 | D | 4 |
E | 5 | F | 6 |
G | 7 | H | 8 |
the character variables of the first obs (the second row) become missing (blank)
txt1_label | num1_label | txt2_label | num2_label |
1 | 2 | ||
C | 3 | D | 4 |
E | 5 | F | 6 |
G | 7 | H | 8 |
along with a warning message:
WARNING: During insert: Data type value could not be converted for column
The problem only occurs in the first obs AND the character variables AND dbms=excel.
Everything goes fine if I choose dbms=CSV, but this is somehow inconvenient for my work.
Test code:
===============================
/* create a simple dataset */
data a;
input txt1 $ num1 txt2 $ num2 @@;
label txt1 = 'txt1_label';
label num1 = 'num1_label';
label txt2 = 'txt2_label';
label num2 = 'num2_label';
datalines;
A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8
;
run;
/* export xls */
proc export data = a
OUTFILE= "D:\a_xls.XLS"
DBMS=EXCEL LABEL REPLACE;
SHEET="AAA";
NEWFILE=YES;
run;
/* export csv*/
proc export data = a
OUTFILE= "D:\a_csv.csv"
DBMS=CSV LABEL REPLACE;
run;
=================================
Log of the excel part:
===============================
958 /* export xls */
959 proc export data = a
960 OUTFILE= "D:\a_xls.XLS"
961 DBMS=EXCEL LABEL REPLACE;
962 SHEET="AAA";
963 NEWFILE=YES;
964 run;
NOTE: File "D:\a_xls.XLS" is removed for replacement.
NOTE: File "D:\a_xls.XLS" will be created if the export process succeeds.
WARNING: During insert: Data type value could not be converted for column: txt1_label : Data type value could not be
converted for column: txt2_label
NOTE: "AAA" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.39 seconds
cpu time 0.15 seconds
===============================
SAS version: 9.3 TS1M2 x64_7pro,
OS version: win7 x64 enterprise (6.1.7601)
Can anybody solve this? Thanks in advance.
Try this code, with MIXED=YES statement .
proc export data = a
OUTFILE= "D:\a_xls.XLS"
DBMS=EXCEL LABEL REPLACE;
SHEET="AAA";
NEWFILE=YES;
MIXED =YES;
run;
Took no effect as expected.
AFAIK, MIXED =YES works for mixed data type (i.e.numeric and char) in a COLUMN instead of a row.
log:
550 /* export xls */
551 proc export data = a
552 OUTFILE= "D:\a_xls.XLS"
553 DBMS=EXCEL LABEL REPLACE;
554 SHEET="AAA";
555 NEWFILE=YES;
556 MIXED =YES;
557 run;
WARNING: This MIXED statement is not supported and is ignored in Export Procedure.
NOTE: File "D:\a_xls.XLS" is removed for replacement.
NOTE: File "D:\a_xls.XLS" will be created if the export process succeeds.
WARNING: During insert: Data type value could not be converted for column: txt1_label : Data type value could not be
converted for column: txt2_label
NOTE: "AAA" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.36 seconds
cpu time 0.18 seconds
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.