topic Re: Specify format for date fields exporting to CSV in SAS Programming

Specify format for date fields exporting to CSV

the_yeti — Sat, 12 May 2018 17:56:19 GMT

I'm trying to export a SAS dataset (.sas7bdat) to CSV via SAS, and I came up the code below:

    libname xpto '/project/input/';
    
    data My_Data;
       set xpto.myinputfile;
       format _ALL_;
    run;
    
    proc export data=My_Data
       outfile='/project/output/output.csv'
       dbms=csv
       replace;
    run;

This works and the CSV file is generated. I'm using `format _ALL_` on purpose because I want to remove all formats.

However, on the case of date fields, I'd like to specify an output format, such as `yyyy-MM-dd HH:mm:ss` for example, because with the code above it exports the date fields as integers.

Re: Specify format for date fields exporting to CSV

Kurt_Bremser — Sat, 12 May 2018 19:15:50 GMT

Keep the formats (no format _all_; statement), and run the proc export once. Copy the export data step from the log and adapt it to your needs. Use that custom data step from then on.

Re: Specify format for date fields exporting to CSV

art297 — Sat, 12 May 2018 20:47:46 GMT

I don't know if the following is fool proof, but it worked for my test data. It is an attempt to find all variables that don't have a date or datetime format assigned and remove the formats (if any) from those variables:

data have;
  informat date date9.;
  informat datetime datetime.;
  format date mmddyy10.;
  format datetime datetime.;
  format number best32.;
  input name $ year date datetime number;
  cards;
John 2018 12May2018 12May2018:14:39:00 12557
Mary 2017 6Jun2017 6Jun2017:14:39:00 20192
;

proc sql;
    select name into :deformat
      separated by ' '
        from dictionary.columns
          where libname='WORK' and
                memname='HAVE' and
                (type eq 'char' or
                 format eq '' or
                 sum(missing(input(putn(21071,format),mmddyy10.)),
                     missing(input(putn(1820534400,format),datetime.))) eq 2 or
                 (format ne '' and type eq 'num' and
                  not(year(input(putn(21071,format),mmddyy10.)) eq 2017 or
                  year(datepart(input(putn(1820534400,format),datetime.)) eq 2017))))
  ;
quit;

data My_Data;
  set have;
  format &deformat.;
run;
    
proc export data=My_Data
  outfile='/folders/myfolders/output.csv'
  dbms=csv
  replace;
run;

Art, CEO, AnalystFinder.com

Re: Specify format for date fields exporting to CSV

the_yeti — Sat, 12 May 2018 20:08:56 GMT

Thank you Kurt.

That would work well if I was trying to tailor to one specific file format, but the reality is that I need a generic SAS script that can export any kind of SAS dataset to CSV (without knowing which fields it contains)... The script above works very well for what I need, with the exception of date fields, which is exported as integers - and therefore requires the receiving application of the CSV to "know" which fields are date/time coming from SAS, and convert them appropriately.

Is it possible to have more control over the export process, so that I can have something like (pseudo-code):

if (field is date) then format="yyyy-MM-dd";

Re: Specify format for date fields exporting to CSV

Kurt_Bremser — Sat, 12 May 2018 20:52:26 GMT

Writing a custom data step is a very minor task compared to writing the documentation that accompanies the file anyway. At least in my experience.

Re: Specify format for date fields exporting to CSV

art297 — Sat, 12 May 2018 21:56:04 GMT

@the_yeti: Have you tried the code I suggested? I think it will do what you want for all date and datetime fields with the exception of user defined formats that use a picture format.

Art, CEO, AnalystFinder.com