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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.