05-12-2018 01:56 PM
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.
05-12-2018 03:15 PM
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.
05-12-2018 04:08 PM
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";
05-12-2018 04:52 PM
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.
05-12-2018 04:02 PM - edited 05-12-2018 04:47 PM
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
05-12-2018 05:56 PM
@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