DATA Step, Macro, Functions and more

SAS formats/format library with SQL server data

Reply
Occasional Contributor
Posts: 9

SAS formats/format library with SQL server data

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
Frequent Contributor
Posts: 91

Re: SAS formats/format library with SQL server data

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
Occasional Contributor
Posts: 9

Re: SAS formats/format library with SQL server data

Al,

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

liz
Valued Guide
Posts: 2,175

Re: SAS formats/format library with SQL server data

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)
Frequent Contributor
Posts: 91

Re: SAS formats/format library with SQL server data

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

Al
Ask a Question
Discussion stats
  • 4 replies
  • 142 views
  • 0 likes
  • 3 in conversation