Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 17

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

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_labelnum1_labeltxt2_labelnum2_label
A1B2
C3D4
E5F6
G7H8

the character variables of the first obs (the second row) become missing (blank)

txt1_labelnum1_labeltxt2_labelnum2_label
12
C3D4
E5F6
G7H8

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.

Attachment
Occasional Contributor
Posts: 12

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

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;

Occasional Contributor
Posts: 17

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 1421 views
  • 0 likes
  • 2 in conversation