Best practices for setting formats and informats? +ODS question

[TurnTheBacon]

1) Can someone please direct me to a source where I can learn "best practices" for setting suitable formats and informats?

2) Is it correct that ODS MARKUP is the ideal way to format a .CSV-file? I only want to set basic column width and headers for the Excel report.

Thanks.

[Patrick]

Do you want to use DI Studio for this?

[Cynthia_sas · Accepted solution]

Hi:

  Not sure about #1, what you mean by "suitable" formats and informats. I have always found my formats and informats to be dictated by 2 things:

1) input task (reading a data file into a SAS data set or creating informats for data transformation purposes) (INFORMATS)

2) display task (displaying my data for reporting purposes or creating formats for data transformation purposes) (FORMATS)

  But for #2 -- ODS CSV, ODS CSVALL and ODS TAGSETS.CSVBYLINE are 3 ODS MARKUP destinations that allow you to create CSV files from SAS procedure output (such as a PROC PRINT of a SAS dataset or directly from one of your other SAS procedures). However, ODS is not the -only- way to create CSV files. PROC EXPORT can create a CSV file from a SAS dataset. You could write a DATA step program to create a CSV file from a SAS dataset and/or you could look for some older macro programs and tools, like the older %DS2CSV macro program.

  However, you said that you want to set "basic column width and headers for the Excel report" -- CSV -- whether you use PROC EXPORT or ODS -- has NO way to set column widths, no way to set any header attributes beyond using the variable name or variable label.

  If you wanted to explicitly set column widths and/or colors or fonts, you would have to use other ODS destinations, such as ODS MSOFFICE2K and/or ODS TAGSETS.EXCELXP. These two destinations are also ODS MARKUP destinations. In fact, any ODS destination that creates explicit "markup language" output, such as ODS HTML, ODS, MSOFFICE2K, ODS CSV, ODS LaTeX, ODS PHTML, ODS CHTML, ODS TAGSETS.EXCELXP etc, etc are ALL ODS MARKUP destinations. Using ODS MARKUP is a great way to create markup language result files by just changing the tagset template that's used by the destination.

  See the code and screenshot below for an example of setting width for a LONG column value. The NEWNAME variable is rather long and you can see in the CSV file that a default column width is used by Excel when the CSV file is opened. There is different behavior with ODS MSOFFICE2K and ODS TAGSETS.EXCELXP because the CELLWIDTH style override is used by Excel when it opens the XML file (created by TAGSETS.EXCELXP) or the HTML file (created by ODS MSOFFICE2K). And, for an extra wow factor, I change the header colors with a style override too.

cynthia

data class;

  length newname $80;

  set sashelp.class;

  newname = catx(' ',name,repeat(substr(name,1,2),7));

  newname = propcase(newname);

run;

** ODS MSOFFICE2K is really making an HTML file;

** but we want to open it with Excel so give it an XLS file extension;

** ODS TAGSETS.EXCELXP is making an XML file, but Excel 2003 and higher;

** knows how to open this XML file.;

ods tagsets.excelxp file='c:\temp\xp.xml' style=sasweb;

ods msoffice2k file='c:\temp\mso.xls' style=sasweb;

ods csv file='c:\temp\comma.csv';

proc print data=class noobs

  style(header)={background=pink foreground=black};

  var newname /style(data)={cellwidth=2.5in};

  var name age height weight;

run;

ods _all_ close;

[TurnTheBacon]

Couldn't have asked for a better answer than that. Thanks Cynthia, very informative.