topic proc export dbms=excel, character variables of first obs (second row) missing in SAS Procedures

proc export dbms=excel, character variables of first obs (second row) missing

bill0101 — Thu, 21 Nov 2013 09:31:15 GMT

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.

Re: proc export dbms=excel, character variables of first obs (second row) missing

SOORISAS — Thu, 21 Nov 2013 09:46:34 GMT

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;

Re: proc export dbms=excel, character variables of first obs (second row) missing

bill0101 — Thu, 21 Nov 2013 11:30:44 GMT

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