 Okay, hello and welcome to Deathwench's SAS blog, and this one is about how to import a CSV into SAS with really good metadata, which you normally won't have. So let's start by opening SAS, so I'll go over here and open, why is SAS never on my SAS? Here we go, SAS, okay, let's just make it nice and big, all right, and then let's start by going to open our metadata. So I have it here, you can open it from my blog, and here we go. So what is metadata? This metadata has two tabs, it's got one with the tables. So as you can see, I generated some fake data about some animals, and then I instantiated it over here on this data tab, so this data dictionary is for that tab. See, I wanted you to notice that there's types, there's numeric and character here, and you also have, like, the SAS informat and the SAS format, so the informat is best 32 and format is 12 for num, the field length is 8, then for character the informat is dollar sign 20 and SAS format is dollar sign 20 because the field length is 20. You know how these are num 8, that's what you'll get in a PROC contents, you'll get a car 20 in a PROC contents here. So that's what it's supposed to look like, and here's a numeric date, and here's the format for that. So this is the way metadata should look for SAS, at least include this kind of information. So when a person goes to import this, I mean, it's nice to even give them some import data, but if you don't do that, at least you have this. So now we're going to go back to the actual fake data I made, where is it? Oh, it's over here, and so you can open this fake data from my blog, and now we're going to download as this CSV, so that's comma separated value. So I'm saving this file in a place where I know of, it's called downloads, and this one's going to call, it's called four, input data set four. So remember, a comma separated value file is actually just a string of bunch of stuff separated by commas that can be easily read by a lot of different things, including Excel. Let's go and import this. It's easily read by SAS and base SAS too, so we're going to go to import data, and we're going to choose CSV here, next, okay, where's the file located? So you need it to know where you put it, I put mine here, and so see what it's called, it's called blah blah blah import data set data four, and then it's going to put in library work, which is our online library that closes, when we close SAS it goes away. So I'm going to name this A because you have to name it something, and then just ignore this and put finish. And as you can see, it looks like nothing happened, but we're in the editor, if you go to the log, look at all this excitement. So as you can see, it wrote code for us. So now we're going to scavenge that code, so let's hit the alt key. Let's go up here, and we're going to go where we can strategically start selecting, and we're going to miss this little guy here, we don't want any of this, that's why the alt key, we don't want these line numbers. So we select everything here, that's nice, and we'll do edit, copy, or control C, go over to our editor, control V. Now let's just put some spaces in here so we can see what's going on. So remember that we have for every field, in format, format, and input, and it's a little easier to start with the input. All you have to do here is make sure that the names are right and that the dollar signs are by the character ones, and that's the way we were. ID, performance score, and date of birth, we're all numeric. But if you can see here, these are short, like this name is supposed to be, what is it supposed to be, it's like, if you look at your metadata, it's supposed to be 20, and this is supposed to be 20, usually this comes out of the native database, the native database is where it's storing this data, you know how big those fields are, and so this is how big this is supposed to be. So if you go into your libraries and you go into work, you can see what happens, so my friend has a cat named Dr. Pepper, but Dr. Pepper down here, he's Pepper's cut off. Why? Because pounce are up here, he's shorter. And SAS makes a bad guess, you know, a guess that this field was too short, so we're going to close that, and we're going to go edit this, so now this is going to be 20, and this is going to be 20, oops, not 120, and this is going to be 10. Now again, you have to go on here and do it too, so you do the in format, the format, and the type. And then this was all right, and then now you've got some code, so why don't you save it, so we'll save as, I saved my code as test, we'll just save over that. So now we're going to run this again only, I want to clear the log first, so we'll go over to log, right click, choose edit, clear all, just to keep things easy, and now we're going to go run our code here. So now we're going to go back to our logs and see what the log says. Well, it's sure enough it says, it's nicely spaced now, because we wrote the code, and everything looks fine there, let's look and see if Dr. Pepper is all together, and it looks like he is, here he is. Now you'll notice that Dr. Pepper's got a performance score, this performance score is a number, he's got a performance score of two, I don't know why, it's so low. But I'm going to right click and clear this, I'm going to show you what can go wrong here, and let's just close this code here and open a new program. I'm going to go and edit this data that you can't edit, and I'm going to go turn Dr. Pepper into an A. So what's this going to do? This means now performance score, says it's going to have to guess it needs to be a character field, because it's not going to be able to fit this character in that numeric field. So it says all changes saved, now let's save this, kind of screwed up data set with that weird A in there, and see how SAS reacts to that when we run the import wizard. So now this one's five, right? So we're going to go back here, and go back to our import wizard. So file import, again CSV, that didn't change. But now we're going to run into problems, let's see here. Here's our number five, and next, work A. Now you'll notice it'll ask you to replace it when you go finish here, right? No problem. Okay, again it'll look like nothing happened, but you go to the log, and this time you aren't as happy, because you see import and success. And you can see I sort of pretended what would happen here. Performance score, it still thinks it's best 32, remember that's number, but that A screwed everything up. And you can see it here, here's Dr. Pepper with his A. Down here this A is no good. So what do you do when that happens in real life? Well, the goal is to figure out a way to read in that column as a character field, and muck with it later in code. Sometimes that's really hard to do, because you're reading in something fixed with, and you don't actually know how wide that column is. And so it depends on the source of your data and how your data are coming in. But if there's any way that you can figure out the width of that column and read it in as a character field, you'll be fine. And that's a lot of trial and error, and you can use different editors to try and do that, but that's one problem with SAS. Not just the import function, but just in general, it's just a little hard to figure those kind of things out. But if you have a smaller data set, you can always just open it and take a look at it, and maybe you'll be able to see what the problem is. All right, well, that's a crash course on how to import a CSV into SAS.