Validating Flat Files

There are still a lot of flat files out there. And there are a lot of SAS programmers who have to deal with them. And unfortunately, there are a lot of people who create flat files that don't understand their structure, or lack thereof.

I worked for a company that had a lot of vendors providing flat files of data. These vendors thought nothing of randomly expanding/shrinking columns and shifting data all over the place. Their DB was able to handle it, no problem. But when the flat file would get passed to us it would cause all kinds of headaches. What to do?

Check the dates. Try to get a date at the end of the record, read it in using a date informat and then check the result. If anything gets shifted around you either get a date that is very unrealistic or missing. Sure it's pretty simplistic, and not totally foolproof, but every programmer that saw it understood it. And more importantly, because it took 2 minutes to code, everybody used it and incorporated it into their own code. And suddenly life was better.

What's missing?

Here's some syntax I ran across today that I hadn't seen before:

data something;
set somethingElse( where= (someVar is not missing) );
run;

Usually I am skeptical of new syntax that duplicates the functionality of existing syntax. It causes language bloat and ends up adding unneeded complexity. But I think this is pretty useful. It looks a lot better than the alternative '' or ' ' or . for missing. And it solves ambiguities between numeric and character missings. I only have one complaint:

where= (someVar is not null)

Language bloat. I hate language bloat. Is the keyword null the same as the keyword missing?

Why do I hate language bloat?

If you are trying to figure out data discrepancies in a large system written by others and you run across both keywords, you will have to spend some time trying to figure out if SAS does indeed handle them the same by either writing test cases or looking through documentation.

Windows XP Tweak

If you use Windows XP, go here:
http://www.microsoft.com/windowsxp/downloads/powertoys/xppowertoys.mspx

And definetly download CmdHere.exe.

It allows you to open up a DOS command prompt from windows explorer and the command prompt automatically points to the selected folder.

Quite possibly the most useful thing you will do with XP the whole day.

Bandaids

Today I am working on V6 to V8 issues. The client I am working with has a whole system written in SAS: AF, BASE, STAT, pretty much a mixed bag.
And man oh man, the V6 to V8 conversion has been a major source of headaches. Here's what we're working on:
AF screens capture and validate user input, some BASE SAS code is created and gets run using the AT command. The code does some
subsetting and then creates the reports the user asked for. Pretty standard stuff. Here's the problem: the proc gcharts use the NOZEROS option
to keep the proc from creating empty bar charts. In V6 the option causes the proc to fail if a group can't be drawn and then it's on to the next step. In V8, the option now causes a syntax error(!!!??) when a group can't be drawn. SAS sets obs = 0, checks syntax, and doesn't run any of the subsequent steps. Which means some of the reports don't get created.

Is this little change documented anywhere? Can anyone explain to me why an option should cause SAS to behave as if there were a syntax error?

The fix:
options noSyntaxCheck; I'll give you five minutes to find that in the documentation.

Ding! Time's up. Don't feel too bad if you can't find it. I couldn't either. I don't know how I was ever productive before google.

FTP- Back To Basics

Okay, let's be honest here. The more you learn and the more specialized you become as a programmer, the more of the 'basics' you tend to forget. There are a couple tools I believe every programmer should have at their fingertips and basic FTP is one of them. Have you met the programmer who can't move a file around without SAS/Connect proc upload/download or some GUI FTP client? I have, and believe me, you don't want to be that programmer!

So, let's review File Transfer Protocol.

First of all, get to a command prompt. At your prompt type:
> ftp
This will start the FTP client. In FTP you open and close a connection to another machine. For my example the remote machine will be called fat_tuesday.
> open fat_tuesday
Now give your username and password.
Once you are connected to the remote machine you can put files onto the remote machine or get files onto your local machine. The local machine is ALWAYS referring to the machine you launched ftp from, not where you happen to be sitting! To put a file from my local machine to my fat_tuesday server I type:
> put myFile.txt
To get a file from the remote machine to my local machine I type:
> get someFile.txt
When I am done I can close the connection:
close fat_tuesday
And close the ftp client:
> bye

That's the basics. Here's some other useful commands, and remember you can always type help in ftp to get a list of commands.

mput -- multiple file put, can glob filenames (mput *.jpeg)
mget -- multiple file get, can glob filenames (mput *.jpeg)
prompt -- turns confirmation on/off for multiple file actions
bin -- change the file type to binary
cd -- change directory on the remote machine
lcd -- local change directory
ls -- list file (or use dir)

Valid Complaints?

Say you are programming away in SAS and you need to switch every occurence of a word with another word. You look at the SAS functions and happily see TRANWRD().

From the documentation: TRANWRD(source, target, replacement).

No problem; you stick it in your data step and it's on to the next thing. And so next you find that you need to get rid of some bad characters. You already know you can compress() the little buggers out (or maybe you didn't know? Compress() can be used to compress any character- not just spaces! There's an optional third argument where you can specify which character(s) you want to remove.) But that presents a little problem since it may cause some wordsToRunTogether. And you don't want that.

So you look in your documentation and find the TRANSLATE() function replaces specific characters in a character expression. Woohoo we are saved! We can replace bad characters with spaces. But check this out: TRANSLATE(source,to-1,from-1<,...to-n,from-n>)

Translate to from.
Tranwrd from to.

It leaves me scratching my head. Why?

And since I brought up the trandwrd() function and complaints, I really should point out that tranwrd() DOES NOT replace WORDS. But rather a pattern of characters. What!? If you want to translate words with the tranwrd() function you have to provide your own spaces.
* get rid of preposition FOR;
myText = tranwrd(myText, 'FOR', '''); * oops, FOREST turns into EST;

myText = tranwrd(myText, ' FOR ', '''); * now translating the word FOR;