Australian merino wool sweaters, Russian watches, Swiss chocolate, Chinese silk blouses, Canadian country music, American sweatshirts, English knitting patterns, Californian pistachios, French muesli, Egyptian bracelets, Scottish kilts, the list goes on.

Was I born in a king’s family? Not really, much as I’d like to think of my dad as royalty, he did work his way up. Thanks to this wonderful Dad who traveled the world on work and diligently saved his per diem to get his family and friends generous souvenirs so we could sample a bit of his life abroad.   Russian watches not Swiss because at that time Russia in all her glory had the best workmanship. I guess we got a little used to the global economy already. So the move to Canada & eating tropical mangos today came as a lovely childhood reminder of all things global available locally.

Why is it important to think globally?

I’m positive you’ll all agree that the choice is out of our hands. Workers are migrating, borders are shrinking and language is flexing her muscles and changing and adapting. When sushi is a natural lunch option and your coworkers easily get hooked on the spice you bring to the table; when you only need a Landline for family conversations; when your iPhone or blackberry is your phone, your music store, your address book, camera, calendar, calculator, internet browser and computer all in one - you know you’ve already arrived in the new age.

In the new world, can you count on SAS to lead the way? You bet.

A student wanted to read time in hours, minutes and seconds from a text file into SAS and then run a query against this table using PROC SQL. Is it Possible? He asked, of course I replied, SAS has no problem switching effortlessly between raw data, SAS dataset & SQL. Here is his problem:

First day back at work after training, I stumbled upon this problem.  I’m currently porting my pure SQL code to SAS using PROC SQL.  Of course I have to makes some code changes. One big difference is the type conversion between variables. Here’s a problem I couldn’t solve. My number 0635237 stands for 6:35:23.7. How can I convert the number into SAS Time? That’s not all. Later I want to query the number using SQL and I want it to display like 6:35:23.7

Here’s my solution:

Use an informat to read in data. Informats are these smart SAS creations that perform a 2-for-the price-of-one role. And there are tons of Informats to choose from.

For one, you use them to read in raw data. No tall claim that SAS can read any data. You can even create your own informats. And this one  takes your time in hours, minutes and seconds and converts it to seconds. Why the conversion? So you can perform calculations. Maybe you’d like to see the difference between order time & delivery time. I used the B8601TM informat which reads time values that are specified in the ISO 8601 basic time notation hhmmssffffff where:

hh is a two-digit hour (zero padded), between 00 and 23
mm is a two-digit minute (zero padded), between 00 and 59
ss is a two-digit second (zero padded), between 00 and 59
ffffff are optional fractional seconds, with a precision of up to 6 digits, where each digit is between 0 - 9

There you go. SAS read in your raw data file easily converting your time into seconds using an informat. Then it smoothly spoke to SQL instructing it to print out the now SAS dataset using a SAS format (FYI you can extend the value of your PROC SQL output by using SAS formats).

Now you know the SAS informat is one of my favourite things. Have you used a cool informat recently that you’d like to share with me? What is your favourite SAS thing? I’d love to hear from you.

Tags proc sql
Share