 Well, hello and good day. I'm so happy to see you today. I'm Monica Wachee and here we are today talking about SAS again and this today's live stream is SAS Access Gateway to Other Applications and the weather's ugly outside, but we're excited in here because we're going to talk about integrating SAS with other applications and thank you for coming today. Thank you if you're just joining. Oh, welcome Derek. Thanks for coming and make sure you download the slides and also I'm going to be looking at the chat to see if you have any questions and also you can ask questions at the end because I'm not in a hurry. I'll answer anything you want. So, so I'm a data scientist, but I'm an epidemiologist. So when I went and got my master's degree, they taught SAS and so I learned it in school, right? And so what happens is that SAS is an old program and SAS kind of likes to be in its own environment. That's the way it's always been. But SAS has these different components. And actually, since the early 2000s, we've been able to connect SAS with other environments. It's just people haven't really always done it a lot, right? Now you kind of need to learn how to do it because if you want to move your SAS data from a SAS server into the cloud, like into Snowflake, you're going to have to use the SAS Access component. So it's a tool that you can use to connect to other databases. And I'm saying, especially Microsoft SQL, I'm going to talk about two use cases in this little luxury, but there's a lot of other use cases. The two I'm going to talk about is the first one is sort of organizational, like if there's a SQL, a Microsoft SQL server, and you've got like an instance of SAS, maybe you've got PC SAS on your desktop at work. This is literally what happened to me. And you can use the SAS Access component to go connect to that SQL server, and I'll show you what I mean, in an organization. And then the second use case I'm going to show you is where you're just maybe at home on your computer or on your laptop and you've got PC SAS, excuse me, and then you've got like an Excel spreadsheet of data, like data, like you downloaded from SurveyMonkey or RedCap or something. You know, it's not just random Excel, you know, it's form formatted with columns and stuff. So you could interact with it like it was a data set in SAS. So those are the two use cases I'm going to go over today, because a lot of people are like SAS users. See, SAS is such a big program, you could use it forever, and you don't know everything about it. And this is something that I actually did. So that's why I know about it. But it's also something that's, excuse me, really important now that people are making more application pipelines and trying to put SAS Viya in there. And this is what you would do the same thing with SAS Viya, theoretically. Maybe the coding is a little different. But I'll go over the terminology here. So ODBC stands for Open Database Connectivity. So think of it as like a plug that you can plug other things into, right? And the ODBC is an API, which is application programming interface. So APIs are used to connect applications together. So this is basically connecting database applications together. You have an API called an ODBC, okay. So now, if you didn't know that, you can feel all smart that you learned some terminology today. All right. And so the SAS access component lets you open an ODBC connection to other types of databases. Now, this is sort of easy to imagine if you think of like a SQL database, because you can kind of imagine tunneling into a SQL database and like doing a query, querying some data out or extracting some data out. But it's kind of weird when you think of Excel, right? And also, it's kind of weird thinking about uploading data. Well, maybe not uploading, but like manipulating data, like updating just a column in a data set. And so you might be like, okay, well, how's that going to work? So if you download the slides, which I keep putting this in the chat so everybody makes sure they can download the slides so you can get these wonderful links here. These are two SAS white papers. And I probably should have put them in the other order. The first one covers the Excel use case. And the second one covers the SQL use case. Okay. And interestingly, the first one uses data step language to do the data manipulation. And the second one uses ProxSQL. So you're getting a hodgepodge of information today. All right. So first, I'm going to talk about the example where you work somewhere, they've got a SQL server. You've got your SAS instance, like your PC SAS, and you're like, can I go into the SQL server and query out like the patient table or the encounter table or something. So my experience is you want to be friends with IT. I'm always friends with IT. And it's because they're going to have to go and allow you. They're going to have to give you an account that allows you into the SQL server. Okay. So you can't just go, oh, I know about the SQL server, I know where it is, and then try to do this. You're going to have to be allowed to log in. Okay. And the screenshot here is from one of the white papers from the second one, the SQL one. And it looks very Windows XPE. But this is basic. So basically, an example of what the IT administrator has to do is go in and set up this the ability for you to connect there. Now, this diagram just kind of shows you what's going on. So imagine this is XYZ company. And this is a physical environment, like, like maybe a clinic, you know, or a, like a office building with medical people in it. And this is actually a physical server room, because I don't know, HIPAA, whatever. So there's a Microsoft SQL server. And then over here you've got IT department, right? So the network administrator says, okay, I know you're a SAS user, you're okay over here. I'm going to set up this. Okay. So then you go back to your desk, and you, you open up SAS, and you do code in SAS that allows you to log into the SQL server, which has a whole bunch of tables in it. So you have to know where you're going. You have to already know about the tables in the SQL server and know their names. And so in practice, what the database administrator would do was give me read access to the SQL server. So I could just look, like, log into the SQL server, like in SQL, and just, like, look at the tables and see their names and stuff. Because then I was having to program this in SAS. You'll see what I mean on the next slide here. So this is from the SQL paper. I just took some screenshots of the code. Because, you know, a spoiler alert, I'm going to show you something I was going to try to do for you. It's always cool if I can run code in my live streams, but I kind of wasn't able to, and I'll show you why. But for now, I knew I would never be able to show you a SQL server situation because I don't have an IT department to do that. So I just took a screenshot from the SQL white paper. And so this person's name is Ross, I guess. And so first of all, I just want to say, as much as I love SAS white papers, they are so confusing. Like, they choose the weirdest, like, using the term my data to mean your data is always confusing. Like, why not use a real use case? Why not just make up a scenario, tell me what the variables are the, everything is named, try to name it like something realistic, and then I'll understand it. But this one here, it says libname Ross odbc dsn equals Ross, right? Like, as you can see here. Okay. And what I believe is happening is they're setting a libname. See, I would have called this something else. I think a libname is Ross, but the DSN is the data source network. I'll probably screen that up. But that's what that's what's been set on the server to allow Ross to log in. So he's in PC SAS and he's setting the libname. Now he's using prox SQL. Okay. And actually what he's doing here is just creating a blank table. So he's just creating a blank table called it in Ross and his libname. That's just empty. Okay, and see it says subject ID. I hate it when they use subject white blood count, red blood count tab, and you can kind of see this is and then quit. Like if you're not used to using prox SQL in SAS, you just you call it here you put all your SQL statements and then you put quit. It's kind of like run. Okay, but and you can do this with data steps to but he just didn't SQL. So we'll go to the next slide and see this is how he takes the data from the odbc connection and loads it into that empty table. He just made says create table Ross table as select all from Ross. table lab and I think because he made that one it's going to inherit those characteristics. And then he points out that this answers is a wild card but those of you are used to SQL are used to seeing the select all kind of thing. So the author sets this local libname and the sex of SQL data into his table. And so this is one of many examples of how you can use the SAS access component. But they mostly have to do with bringing data into the SAS environment from somewhere else. And this is a strategy for minimizing I SAS IO costs so imagine a big SQL server. Well, you can do is create a view in the SQL server. And then that's basically what we did is then I hit the view. So I would come in with my SAS PC SAS and just upload data from the view and also, I mean they trusted me but they could make it so that the view was the only thing I had access to. I think they gave me a count where I was not very trusted so I can log in because I was always worried about accidentally updating the data. I was like, don't read access. I don't know what I'm right access. You know, just let me read it like I designed stuff, but I don't do stuff in SQL and I just don't want to break anything, you know, but anyway, so I wanted to show you the Excel version of this. I was going to go over to SAS ODA. This is SAS on demand for academics and it's free. And it's online. Well, you get what you pay for. I went here earlier and it was down. Okay, the permanence will they maintain it. And so if you launch, you have to set up an account and I signed in earlier, which is why it's doing this pass through authentication. Of course, it takes forever. But I just wanted to show you this is the SAS ODA interface. And I just happen to have some code open here. You know, I use this for demonstrations, but I wanted to use this to make an ODB C connection to some sort of Excel file, whether in this environment or outside of it. And I just couldn't do it in SAS ODA, but you probably I'm sure you can do it in PC SAS, especially if you're on your own machine in year and admin, you know, it's basically like you get to set up your own. But anyway, so I wanted to show you that, but I couldn't. So I'm just going to talk you through the example from the white paper, which is really not that great, right. And so, so in the white paper, they're saying that you have to get these parameters ready. So remember Excel, Excel is a spreadsheet, and you have different tabs, right. So let's say you have this Excel spreadsheet, and it's called, I guess demo dot XLS. Okay, so you have to have that name, and then you have to have the name of the, the tab that the data on, of course, they choose the name sheet one, for example. And I would not have done that because that's confusing. And then they say that you need to set a named range, right. So, um, I, one of my earlier live streams I demonstrated analyzing some data from an online list of, of articles about violence on Black Friday and I don't know there was like 40, like 40 rows or so and about maybe 10 columns. So in Excel, you can set a named range. So apparently what you not only have to do is specify the tab but specify a named range on that tab, which I would have called miss something else besides sheet one, because now this is confusing. And then you have to set a lib breath, which is like a lib name, but it's a lib breath. And ODBC XLS is the term that the swipe paper person chose, which is also the DSN, which is the data set name or like the data source network or whatever I was calling it. So here is screenshot from this Excel paper. So I'm just going to read it says let's assume I have Microsoft Excel file named demo dot XLS has a worksheet not named sheet one with a named range called sheet one, right, and that's the data on it. It's ODBC data source name is ODBC XLS. So this is how to assign the lib name ODBC XLS to the data source. And of course a Libre F can be any name you prefer to use. So lib name, ODBC XLS ODBC DSN ODBC XLS is that clear as mud. But in any case, what I remember doing is when I when I actually had PCSAS obviously you don't have it right now is I would just try this stuff until you get it right like that's one of the things that's so funny about my customers is my customers come to me and they're like Monica, this doesn't work. And I'm like, well, did you try this? I just tried things until I get it to work. So I probably would not have chosen these names. This is super confusing. But what I wanted to show you specifically was over here because this author chose to use data steps to do the data transfer. And so here, it's data from XLS set ODBC XLS dot cheat what run. So those of you know data steps know what this is going to do is take this exact named range from this Excel and make it be a data set in the SAS environment named from XLS, which of course I wouldn't have named it right. So this is how you bring date data in from Excel to SAS using SAS access ODBC connection. Now you can also write the date SAS data back to the Excel file. I don't know how this is going to work right so imagine I guess there's a sheet to so this is your output right because it's the first line. Okay, and so to choose the new sheet and demo dot XLS and then set from XLS, which is this thing here. And then it says here new field equals anything add a new field to it if you want to run. Okay, I do not know what would happen if you did that right. So again, so why am I telling you all this I'm telling you all this because it's important to know the stuff about how to set up application pipelines like SAS access is this plug in this component to connect applications together. And in, like, I told you I'm an epidemiologist I've been using SAS for over 20 years. I was trained in SAS as a researcher and I was trained to use SAS for like, like survey data, or like data in epidemiological studies where everything was like, I didn't expect to be gathered and well documented. But guess what is 2024 already, and we have so much data that are from applications, and we have so many choices of what to do with those applications. And so what if you're expected to analyze data from an application, like a medical records application, or like other health applications, like I'm always seeing people bring to me data from like exercise applications, and they're like, what do I do with this? I don't even understand it, you know, because that's coming out of like a SQL server usually, you know, a relational database. So that's why I invented this workshop, application basics, the promise of open source. So even though I was just talking about SAS, what was I talking about doing? Connecting SAS to other things like potentially open source things. So the learning objective of my workshop is to understand data sets from applications well enough to analyze them and produce results. We'll first focus on computer applications, like how they're actually designed, the team structure, and how the data are stored. And I'll go over all this different terminology used in application development. And with this knowledge, you can break through communication barriers and get the answers you need to complete your analysis and be seen as an expert when you're getting data from these applications. So I'll give you some details about this workshop. As I told you, it's application basics, the promise of open source. That's our theme this month. It's Saturday and Sunday, February 24th and 25th. And it's actually to these two sessions, each session starts at noon Eastern time and runs about three hours. And the normal price for a weekend workshop and data science like this, it's about $250 to $750 per workshop. But because you came to my event today, you get to have it for free. That concludes what I have prepared for you today about the SAS access component. So thank you very much for showing up today. And I hope you have a good rest of the day and a good rest of the week. Thank you for watching this video, which is part of the Public Health Today to Science rebrand program. If you are interested in joining the program, please sign up for a 30 minute Zoom interview using the link in the description.