BookmarkSubscribeRSS Feed
Liz_LSU
Calcite | Level 5
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
4 REPLIES 4
advoss
Quartz | Level 8
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
Calcite | Level 5
Al,

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

liz
Peter_C
Rhodochrosite | Level 12
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
Quartz | Level 8
Peter provides an excellent checklist of potential "gotchas". It is definitely not for the faint-of-heart.

Al

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
  • 4 replies
  • 777 views
  • 0 likes
  • 3 in conversation