BookmarkSubscribeRSS Feed
the_yeti
Obsidian | Level 7

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.

5 REPLIES 5
Kurt_Bremser
Super User

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.

the_yeti
Obsidian | Level 7

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";
art297
Opal | Level 21

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

 

art297
Opal | Level 21

@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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5099 views
  • 0 likes
  • 3 in conversation