SAS formats/format library with SQL server data

[Liz_LSU]

I'm very familiar with using format libraries and formats with permanent SAS data sets. I may be involved with a very large project in which hundreds of legacy SAS data sets (most of which include permanent user formats) will be converted into SQL tables.

I know next to nothing about SQL; others with that expertise will be handling that part of the project. I will be responsible for helping get the data out of SAS and into SQL and then revising the existing programs to work with the data in the new form.

In my SAS-centric world, the format library will still exist and each SAS program that analyzes the new SQL data will assign the formats. Is that the best way to handle this, or is there a better way (e.g., a way to assign formats within SQL)?

Thanks,

liz

[advoss]

You might want to consider using PROC FORMAT CNTLOUT=... to export your formats to SAS tables and then convert those table to SQL "code" tables. Then, a simple (relatively speaking) SQL query can join the SQL fact tables to the appropriate code tables. With appropriate indexing, maybe some referential integrity rules, this can end up being pretty efficient.

Al Voss

[Liz_LSU]

Al,

Thanks for the reply. That definitely sounds like something to investigate.

liz

[Peter_C]

watch out for
1
picture formats like user defined date formatting
2
range-matching rather than identity matching (where start and end contain different values)
3
the default (other) labels defined by the cntlout variable HLO containing an O,
and 4
embedded formats where the label is the name of another format (indicated where the HLO variable contains iirc F)

[advoss]

Peter provides an excellent checklist of potential "gotchas". It is definitely not for the faint-of-heart.

Al