Specify format for date fields exporting to CSV

Reply
Occasional Contributor
Posts: 17

Specify format for date fields exporting to CSV

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.

Super User
Posts: 10,217

Re: Specify format for date fields exporting to CSV

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

Re: Specify format for date fields exporting to CSV

Posted in reply to KurtBremser

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";
Super User
Posts: 10,217

Re: Specify format for date fields exporting to CSV

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 8,164

Re: Specify format for date fields exporting to CSV

[ Edited ]

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

 

PROC Star
Posts: 8,164

Re: Specify format for date fields exporting to CSV

@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

 

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