Getting Data Into Excel from SAS

Proc Export vs Tagsets.ExcelXP

Here’s a quick rundown of some of the relevant differences:

Proc Export

  • Native Excel file
  • Fast(er)
  • Multiple worksheets, one file
  • One dataset per Sheet
  • No formats
  • No graphics

Tagsets.ExcelXP

  • XML File, not XLSX
  • Slower than export
  • Multiple worksheets
  • Multiple output per sheet
  • Formatted output
  • Titles/Text embedded

Since Proc Export is well documented and varies significantly between systems, I won’t go into that here.

For Tagsets.ExcelXP I highly recommend the following paper as a reference. It indexes the majority of the Tagsets.ExcelXP features and provides the appropriate page reference in the relevant paper. If you’re having issues with Tagsets.ExcelXP this is the best reference to answer your question.

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

Another common response to Tagsets.ExcelXP questions, where something isn’t work as expected, is that the latest version of Tagsets.ExcelXP is not installed. To download the current version, see here:

https://support.sas.com/rnd/base/ods/odsmarkup/

Pseudocode AKA Stop and Think

From Wikipedia:

Pseudocode is an informal high-level description of the operating principle of a computer program or other algorithm.  It uses the structural conventions of a programming language, but is intended for human reading rather than machine reading.

My definition:

A way to force you to think through your program before coding.  Really thinking through what variables you’ll need, what outputs you’ll need. It helps further on with the design. It allows me to work through some of the decision points that I’ll need to program later on, such as:

  • Should I use a macro variable or by groups?
  • Do I need to keep around the results from every simulation or just the end results? Which results do I need?
  • Where do I need counters?
  • Do I need break logic or define my loop with a do while instead?

This is an ideal situation, and I admit I don’t do it all the time. Sometimes experience allows me to skip this step, and sometimes I think experience allows me to skip this step and I shouldn’t.  There are many times, when I wish I hadn’t.

But basically, its a step that says, STOP AND THINK. Because thinking before doing makes things go faster. It’s also a great way to create a program structure that you can then pass on to someone else to actually code, if that’s an option.

What I’ve Learned From Being a Consultant

Things I’ve learned from being a consultant

Working from the government pays the bills, consulting on the side pays for toys :).

Organization

I’m the only working on this project, so you’d think you can organize it any way I’d want, right? No. At any time the client can ask for anything, including the full code and it is my belief if they’ve paid for it then they should get it. The code is mine, but was developed for them so they can have it. Maintaining code isn’t very much fun so I don’t worry too much about putting myself out of a job.

Time Management

Clients want things with a quick turnaround. Sometimes this means doing things at lunch time or spending time after work. When its my own time and means less tv or vacation time, it gets spent wisely. Also, see Organization. Being organized saves me and the client time. The time taken to organize things is probably equivalent to billing for time spend search for things.

Communication Skills

Rarely does a client ask for what they end up with the first time around, unless its repeat such and such for me.  This means working with clients to flesh out questions. If you get it wrong, then you waste time and that time gets billed to the client.  People generally don’t like paying for work that wasn’t what they requested. Especially, since I’m sure what they said was clear in their mind. Communication isn’t only listening to what a person says, its hearing what they want and sometimes that isn’t said.

Different Tool Sets

If a client uses SPSS, SAS, or R, then that’s what you’re working with on this project.  It’s good to know at least the basics in a few languages and how to look up things in those languages as well.

Design

If I can generate the results on time, on budget that’s great. If those results are copy and paste insert into the manuscript/PDF/powerpoint presentation that’s 10 times better.  I’ll admit some of the designs would never had occurred to me, but come from clients requests.  However, seeing something once I can learn and adapt that design to other uses.

Working in Government – Negatives

  1. Training – There simply isn’t the budget and will to allow staff to develop skills by attending conferences out of the province or country, training sessions and courses on a regular basis. Fortunately, there is now Google and an abundance of on-line courses so I can maintain my skills, but a good portion is at my own expense and time.  I can’t imagine how staff 20 years ago maintained their skill set. In fact, I can see that a lot of staff hasn’t. This is particularly short-sighted initiative from the government. You don’t know what you don’t know and the only way to learn things is to see what other people are doing, including in other provinces and countries. It’s one thing to sit around a room and discuss things, but the conversations that happen at conferences are probably where I’ve learned the most. I’ve also made some great connections, which means when I get stuck with something I can email someone I met in Las Vegas or Montreal and ask what they do in the same situation.  
  2. Speed – The rate of change in government is extremely slow. For someone like me, with not a huge amount of patience, waiting 6 months for the latest version of software or getting a new piece of software is extremely frustrating.
  3. Collaboration – There really is a lack of collaboration within government, with respect to people working together. I’m not sure why this is, or if it’s just the ‘culture’ where I’ve happened to work. I can definitely say the best places I’ve worked, have emphasized collaboration.  When collaborating, there’s always someone to say well-done and why did the hell did you do that. Those simple checks can go a long way in accomplishing projects.
  4. Lack of bonuses – The base pay within government is not bad, but there are no incentives to work harder. There isn’t any options to reward staff who’ve done well, even with a day off, an extra something in the pay check.  If you’re lucky you’ll get a beer and some wings.
  5. Mandatory union and pension contributions – The upsides of being in a union and pension are definitely weighed down by the expenses, specifically the pension contributions. I take home less than 60% of my actual pay check but live in the province with one of the lowest tax rates.  A large portion goes to the pension contribution. When I’m just starting out, I’d probably prefer more to put on my mortgage and get a new car, but I don’t get the choice.  In the long run, I’m not sure this is bad, but right now its a pain in the ass.

Make sure to also read my post on Working in Government – Positives

Working in Government – Positives

5 reasons that its nice to work in government:

  1. Job Stability: I belong to a union. The odds of being laid off are very slim, once I past probation and unless your a total idiot they generally work with you or find a position that fits your skill set.
  2. Contribute to Society: the things I work on have an effect on peoples lives. I can help make decisions that improve peoples lives and future well-being. Most of the time this effect is visible in the short term, though once in a while it will take years to see the effect.
  3. Work – Life Balance: Rarely do I have to work overtime, and if I do, I get the time back. Additionally, I work a very regular 8-4:30 schedule that leaves me time to schedule life. Also, I’m on a alternate work arrangement schedule that allows me to work a little extra every day and then take every third Friday off. That means at least one long weekend every month, in addition to the mandatory statutory holidays. It also means that second jobs are manageable.
  4. Defined-Benefit Pension: Retiree’s today from the governments defined benefit contribution plan have a very healthy payback. This means that you can save less in an RRSP. The contribution levels are high, about 11%, and it remains to be seen if the plan is in place when I’m ready to retire. Assuming I stay with government for that long.
  5. Low Expectations: Let’s face it, the best and brightest rarely go in to government. The salary and opportunities are lacking. There isn’t a lot of competition to be the best in your field or area. This could equally be nice or bad so I’m gonna call it a tie.
  6. Variability: Depending on what Ministry or area your workload shifts as things become popular. This means priorities can shift at a moments notice, but it also means you get to work on different and interesting things.
  7. Make sure to read my post on Working in Government – Negatives

Logistic Regression – Quick Tips

When doing Logistic regression in SAS there are several procedures that you can use:

  • Proc Logistic
  • Proc Genmod
  • Proc Surveylogistic
  1. If you’re using survey weights then you need to use SurveyLogistic to have your variance and confidence intervals calculated appropriately.
  2. For categorical variables, use a CLASS statement and specify PARAM=REF to use referential coding. If you use EFFECT coding (the default option) then your p-values may not align with your odds ratio confidence intervals.
  3. Use the ODDSRATIO statement to get odds ratio for specific variables or to compare different levels.
  4. /EXPB after your model statement will allow you to have the ratios included in your output.
  5. Check the event selection that is modelled in SAS. By default SAS models the event Y=0, when you usually want Y=1. You can modify by specifying the event in your model statement.

So that are some quick heads up regarding using proc logistic in SAS.

A rose by any other name….

I have a Masters in Statistics. This means I spent at least 6 years studying statistics at some university (University of Alberta) and know a few things about stats.  All of this allows me to label myself a ‘statistician’.  However, in the past 9 years that I’ve been working I don’t believe my job title has ever been a statistician.  I have been a:

  • Risk Analyst
  • Resource Analyst
  • Biostatistician
  • Business Intelligence Analyst
  • Methodologist

Biostatistician is the closest title to statistician; all of the positions were very numbers oriented and all of them taught me very different things that have come in useful in my career.  I will say, that even though all of them were numbers oriented, one thing they all required was clear and concise communication skills. Something I’m still working on.