Export converting some text fields to numeric

Reply
New Contributor RAF
New Contributor
Posts: 3

Export converting some text fields to numeric

I need help in trying to better control output of the Exported fields. Some of my text fields are being converted to numeric...

Data (...and it is correctly defined in SAS work.file)

Year, District, Site, Name, Gender

num, text, text, text, num

2012, 003, 02, Raul, 1

2012, 002, 04, Mary, 2

2011, 004, 03, "John, Jr", 1

Desired Text file output:

2012,"003","02",Raul,1

2012,"002","04",Mary,2

2011,"004","03","John, Jr",1

Current text file output:

2012,3,2,Raul,1

2012,2,4,Mary,2

2011,4,3,"John, Jr",1

Current code:

proc export data=work.%mypefile

outfile="C:\DATA\%trim(%mypefile).txt"

dbms=dlm replace;

delimiter='2C'x;

putname=yes;
run;

PROC Star
Posts: 7,363

Re: Export converting some text fields to numeric

I don't know if there is a way to get proc export do to it for you.  I ran your proc export (although I had to correct your leading % to & to get the macro variable to read.  Then, after it ran, I pressed function key F4 to get the code that was actually run.

I modified it as follows, specifically getting rid of the dsd in the file statement and changing the string formats to $quote8. formats:

    data _null_;

    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

    %let _EFIREC_ = 0;     /* clear export record count macro variable */

    file 'C:\art\test.csv' delimiter=','  DROPOVER lrecl=32767;

    if _n_ = 1 then        /* write column names or labels */

     do;

       put

          "Year"

       ','

          "District"

       ','

          "Site"

       ','

          "Name"

       ','

          "Gender"

       ;

     end;

   set  WORK.TEST   end=EFIEOD;

       format Year best12. ;

       format District $quote8. ;

       format Site $quote8. ;

       format Name $quote8. ;

       format Gender best12. ;

     do;

       EFIOUT + 1;

       put Year @;

       put District  @;

       put Site  @;

       put Name  @;

       put Gender ;

       ;

     end;

    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

    if EFIEOD then call symputx('_EFIREC_',EFIOUT);

    run;

PROC Star
Posts: 7,363

Re: Export converting some text fields to numeric

Alternatively, I came across an old post from Mike Zdeb who suggested the following approach:

ods csv file="c:\&mypefile.csv";

ods listing close;

ods results off;

proc print data=&mypefile. noobs;

run;

ods csv close;

ods listing;

ods results on;

Super User
Super User
Posts: 6,502

Re: Export converting some text fields to numeric

You do realize that the distinction that you are making between "001" and 1 or 001 will not be honored by Excel or most programs that read and generated CSV files.  But if you want to create this particular format then you can do it by generating the output yourself.

Here is one method. I used PROC EXPORT to generate the names row, but there are other ways to do that.

data have;

  infile cards dlm=',' dsd;

  length year 8 district site name $10 gender 8;

  input year -- gender;

cards;

2012, 003, 02, Raul, 1

2012, 002, 04, Mary, 2

2011, 004, 03, "John, Jr", 1

run;

filename out temp;

proc export data=have (obs=0) outfile=out replace dbms=dlm;

  delimiter=',';

  putname=yes;

run;

data _null_;

set have;

file out dlm=',' mod ;

format _character_ $quote.;

put (_all_) (: );

run;


545  data _null_;

546   infile out;

547   input; put _infile_;

548   run;

NOTE: The infile OUT is:

      Filename=C:\DOCUME~1\ABERNA~1\LOCALS~1\Temp\SAS Temporary Files\_TD8056\#LN00012,

      RECFM=V,LRECL=256,File Size (bytes)=114,

      Last Modified=19Aug2012:13:25:40,

      Create Time=19Aug2012:13:25:39

year,district,site,name,gender

2012,"003","02","Raul",1

2012,"002","04","Mary",2

2011,"004","03","John, Jr",1

NOTE: 4 records were read from the infile OUT.

      The minimum record length was 24.

      The maximum record length was 30.

Ask a Question
Discussion stats
  • 3 replies
  • 403 views
  • 0 likes
  • 3 in conversation