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

[bill0101]

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.

[SOORISAS]

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;

[bill0101]

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