“They were the best of asteroids, they were the worst of asteroids.” ~ Charles Dickens Armstrong There are good asteroids, and there are bad asteroids. Good asteroids stay in their own neighborhoods and wait for us to come visit them. Bad asteroids, however, don’t wait for an invitation – they [...]
About SAS® dates
Let’s begin with dates. We regularly hear customers say something similar to this: "I have a date, but I’m not sure how to use it or whether it’s even a SAS date yet." No worries--we can figure it out! A SAS date is a numeric variable whose value represents the number of days between January 1, 1960 and a specific date. For example, assume that you have a variable named X that has a value of 12398, but you’re not sure what that value represents. Is it a SAS date? Or does it represent January 23, 1998?
To determine what the value represents, you first need to run the CONTENTS procedure on the data set and determine whether the variable in question is character or numeric.
For this example, here is the partial output from the PROC CONTENTS step:
Alphabetic List of Variables and Attributes # Variable Type Len Format 1 x Num 8 2 y Char 3 3 z Num 8 Z5.
If X is a numeric variable, is a format shown in the FORMAT column for that variable? In this case, the answer is no. However, if the variable is numeric and there is no assigned format, this might be a SAS date that needs to be formatted to make sense of the value. If you run a simple DATA step to add any date format to that SAS date value, you will see that 12398 represents the date December 11, 1993.
data a; mydate=12398; format mydate worddate.; run;
If you print the results of this program with the PRINT procedure, the output for data set A is as shown below:
Obs mydate 1 December 11, 1993
Is this a valid date in the context of this data sample? If you’re unsure, look at the other date values to see whether most of them are similarly structured. Most of the time, if a variable is stored as a SAS date, the variable is already assigned a date format, which is shown in the PROC CONTENTS output. If the value 12398 is a numeric variable such that the digits represent the month, day, and year of a given date (for example, January 23, 1998), you can convert it to a SAS date by running the following DATA step:
data a; x=12398; y=input(put(x,5.),mmddyy6.); format y date9.; run;
The PROC PRINT output from this step shows that the variable Y has a formatted value of 23JAN1998.
Obs x y 1 12398 23JAN1998
The format that you assign to the variable can be any SAS format or custom-date format.
If the original variable is a character variable, you can convert it to a SAS date by using the INPUT function and the MMDDYY6. informat.
data a; x='12398'; y=input(x,mmddyy6.); format y date9.; run;
Using arrays in SAS
Many customers aren’t quite sure that they understand how to use arrays. Arrays are a common construct in many programming languages. Arrays can seem less complex when you remember that they are a temporary grouping of variables. When you perform the same operation on multiple variables, you have less to program if you can refer to a group of variables by a single name. You simply execute a DO loop that processes each variable in turn, and the task is complete!
We often see arrays used for "reshaping data" or transposing a data set from wide-to-long (or long-to-wide). For example, assume that you want to reshape a data set, comprised of three variables and four observations, into a data set that contains twelve variables. Using an array approach makes the programming much easier, as shown below:
In this example:
1. The variables X, Y, and Z are loaded into an array named VARS, which means that they can be referred to as VARS(1) – VARS(3) or by the variable names X, Y, and Z.
2. A multidimensional array named ALL is created with twelve variables. The first number in parentheses represents rows, and the second represents columns.
3. A DO loop processes each variable in the VARS array.
4. The ALL array is populated one observation at a time by the value of I and the value of J as the DO loop increments.
Because the ALL array is populated by each observation as it is read from data set One, the END= option in the SET statement creates the variable LAST as a flag. This variable indicates when the last observation is read, and the IF statement tests variable LAST. If the variable has a value of 1 (which evaluates to "true"), the statement prints the contents of the program data vector to the output data set. Here's the starting data set and the reshaped result:
Managing PC files in client/server environments
When I began working in Technical Support many years ago, the only interface to Base SAS® software was the Display Manager System, which has separate Program Editor, Log, and Output windows. Now, you can run SAS in various ways, and many of our customers use SAS Enterprise Guide and SAS Studio as their interfaces. One of the most frequently asked questions from customers is about how to access local PC files from these applications that access SAS through a UNIX server.
SAS Enterprise Guide offers built-in tasks to upload and download data sets and other files. You can find these tasks on the Tasks->Data menu.
Two of the tasks, Upload Data Files to Server and Download Data Files to PC, allow you to copy SAS data sets directly between your local PC and your SAS libraries. The third task, Copy Files, allow you to copy any file (or group of files) between your local PC and the file system of the SAS session. See this article to learn how to apply a common pattern with this task: export and download any file from SAS Enterprise Guide. (Note: The Copy Files task was added in SAS Enterprise Guide 7.13. For earlier releases, you can follow the steps in this article.)
If you’re using the SAS Studio interface, you can upload and download files between the server and your PC.
To download a file from the SAS server to your computer:
1. Select the file that you want to download from the folder tree.
2. Click the download button and save the file according to the information in your browser dialog box.
To upload one or more files from your local computer:
1. Select the folder to which you want to upload the files and click the upload button.
2. In the Upload Files window, click Choose Files to browse for the files that you want to upload.
3. Select one or more files from your computer and click Open. The selected files are displayed as well as their size. An error message is displayed when you try to upload files where the total size exceeds 10 MB.
4. Click Upload to complete the upload process.
Always go back to the basics
The three topics that are discussed here don't represent new features or challenges. However, these topics generate many calls to Technical Support. It's a reminder that even as SAS continues to add new features and technology, we still need to know how to tackle the basic building blocks of our SAS programs.
FAQs about SAS dates, arrays and managing local PC files was published on SAS Users.
- Messy data. It might come from a variety of sources, and won't necessarily be organized for analytics or reporting. That's your job.
- Nebulous problem set. Usually the goal of a hackathon is to generate insights, improve a situation, or optimize a process. But you don't know going into it which insights you need, which process is ripe for optimization, or which situations can be improved by using data. Hackathons are as much about discovering opportunities as they are about solving problems.
- Team members with different viewpoints. This is a big strength of hackathons, and it can also present the biggest challenge. Team members bring different skills and ideas. To be successful, you need to be open to those ideas and to allowing team members to contribute in the way that best uses their skills. Think of yourselves as the Oceans Eleven of data analytics.
In my experience, hackathons are often a great melting pot of different tools and technologies. Whatever tech biases you might have in your day job (Windows versus Linux, SAS versus Python, JSON versus CSV) – these melt away when your teammates show up ready to contribute to a common goal using the tools that they each know best.
My favorite hackathon tools
At the Analytics Experience 2018 Hackathon, attendees have the entire suite of SAS tools available. From Base SAS, to SAS Enterprise Guide, to SAS Studio, to SAS Enterprise Miner and the entire SAS Viya framework -- including SAS Visual Analytics, SAS Visual Text Analytics, SAS Data Mining and Machine Learning. As we say here in San Diego, it's the whole enchilada. As the facilitators were presenting the whirlwind tour of all of these goodies, I could see the attendees salivating. Or maybe that was just me.
When it comes to getting my hands dirty with unknown data, my favorite path begins with SAS Enterprise Guide. If you know me, this won't surprise you. Here's why I like it.
Import Data task: Import any data
Hackathon data almost always comes as CSV or Excel spreadsheets. The Import Data task can ingest CSV, fixed-width text, and Excel spreadsheets of any version. Of course most "hackers" worth their salt can write code to read these file types, but the Import Data task helps you to discover what's in the file almost instantly. You can review all of the field names and types, tweak them as you like, and click Finish to produce a data set. There's no faster method of turning raw data into a SAS data set that feeds the next step.
See Tricks for importing text files and Importing Excel files using SAS Enterprise Guide for more details about the ins-and-outs of this task. If you want to ultimately turn this step into repeatable code (a great idea for hackathons), then it's important to know how this task works.
Note: if your data is coming from a web service or API, then it's probably in JSON format. There's no point-and-click task to read that, but a couple of SAS program lines will do the trick.
Query Builder: Filter, compute, summarize, and join
The Query Builder in SAS Enterprise Guide is a one-stop shop for data management. Use this for quick filtering, data cleansing, simple recoding, and summarizing across groups. Later, when you have multiple data sources, the Query Builder provides simple methods to join these – merge on the fly.
Before heading into your next hackathon, it's worth exploring and practicing your skills with the Query Builder. It can do so much -- but some of the functions are a bit hidden. Limber up before you hack!
Characterize Data: Quick data characteristics, with ability to dive deeper
If you've never seen your data before, you'll appreciate this one-click method to report on variable types, frequencies, distinct values, and distributions. The Describe->Characterize Data task provides a good start.
Using SAS Studio? There's a Characterize Data task in there as well. See Marje Fecht's paper: Easing into Data Exploration, Reporting, and Analytics Using SAS Enterprise Guide for more about this and other tasks.
Data tasks: Advanced data reworking: long to wide, wide to long
"Long" data is typically best for reporting, while "wide" data is more suited for analytics and modeling The process of restructuring data from long to wide (or wide to long) is called Transpose. SAS Enterprise Guide has special tasks called "Split Data" (for making wide tables) and "Stack Data" (for making long data). Each method has some special requirements for a successful transformation, so it's worth your time to practice with these tasks before you need them.
Program Editor: Flexible coding environment
The program editor in SAS Enterprise Guide is my favorite place to write and modify SAS code. Here are my favorite tricks for staying productive in this environment including code formatting, shown below.
Have another favorite editor? You can use SAS Enterprise Guide to open your code in your default Windows editor too. That's a great option when you need to do super-fancy text manipulation. (We won't go into the "best programming editor" debate here, but I've got my defaults set up for Notepad++.)
Export and share with others
The hackathon "units of sharing" are code (of course) and data. SAS Enterprise Guide provides several simple methods to share data in a way that just about any other tool can consume:
- Export data as CSV (CSV is the lingua franca of data sharing)
- Export data as Excel (if that's what your teammates are using)
- Send to Excel -- actually my favorite way to generate ad-hoc Excel data, as it automates Microsoft Excel and pipes the data your looking at directly into a new sheet.
- Copy / paste with headers -- low-tech, but this gets you exactly the columns and fields that you want to share with another team member.
When it comes to sharing code, you can use File->Export All Code to capture all SAS code from your project or process flow. However, I prefer to assemble my own "standalone" code piecemeal, so that I can make sure it's going to run the same for someone else as it does for me. To accomplish this, I create a new SAS program node and copy the code for each step that I want to share into it...one after another. Then I test by running that code in a new SAS session. Validating your code in this way helps to reduce friction when you're sharing your work with others.
Hacking your own personal growth
The obvious benefit of hackathons is that at the end of a short, intense period of work, you have new insights and solutions that didn't have before – and might never have arrived at on your own. But the personal benefit comes in the people you meet and the techniques that you learn. I find that I'm able to approach my day job with fresh perspective and ideas – the creativity keeps flowing, and I'm energized to apply what I've learned in my business.
The post Essential SAS tools to bring to your next hackathon appeared first on The SAS Dummy.
The concept of "current working directory" is important within any SAS program that reads or creates external files. In SAS, when you reference a file location with a relative path (for example, "./projects/mydata.pdf"), that file reference resolves to an absolute path by way of the working directory. You can control the initial working directory by modifying the shell scripts that launch the SAS process, or by specifying the simple SAS macro that allows you to learn the current working directory. The macro uses a trick to assign a SAS fileref to the current path ('.'), grab the full path of that fileref by using Read the article for the full source (it's only about 7 lines). Here's how you would use it:
56 %put Current path is %curdir; Current path is C:\WINDOWS\system32
As you might infer from my example here, I'm running this on a managed Windows environment. Most users cannot write to the "C:\WINDOWS\system32" path (and would not want to), so any relative file paths in my SAS code would cause errors. Maybe you've seen something like this:
25 ods html file="./test.html"; NOTE: Writing HTML Body file: ./test.html ERROR: Insufficient authorization to access C:\WINDOWS\system32\test.html. ERROR: No body file. HTML output will not be created.
If I want to use a relative path, I need to change the current working directory. Fortunately, there's a simple way to do that.
Change the current directory in SAS
/* working path for my projects */ %let rc = %sysfunc(dlgcdir('u:/projects')); ods html file="./test.html"; proc print data=sashelp.class; run; ods html close;
I can use my account-specific environment variables to make these paths work for all users. For example, on Windows I can reference the USERPROFILE environment variable. (On Unix, I can use the HOME environment variable instead.)
/* working path for my projects */ %let user = %sysget(USERPROFILE); %let rc = %sysfunc(dlgcdir("&user./Documents")); /* create an output data folder if needed */ options dlcreatedir; libname outdata "./data"; ods html file="./test.html"; data outdata.class; set sashelp.class; run; proc print data=outdata.class; run; ods html close;
Here's my log output. Notice how the HTML file and the output data folder are both created at locations relative to my home directory.
25 /* working path for my projects */ 26 %let user = %sysget(USERPROFILE); 27 %let rc = %sysfunc(dlgcdir("&user./Documents")); NOTE: The current working directory is now "C:\Users\sascrh\Documents". 28 29 options dlcreatedir; 30 libname outdata "./data"; NOTE: Library OUTDATA was created. NOTE: Libref OUTDATA was successfully assigned as follows: Engine: V9 Physical Name: C:\Users\sascrh\Documents\data 31 32 ods html file="./test.html"; NOTE: Writing HTML Body file: ./test.html 33 data outdata.class; 34 set sashelp.class; 35 run;
If using SAS Enterprise Guide, you can add DLGCDIR function steps to the startup statements that run when you connect to SAS, ensuring that your working directory starts in a valid location for SAS output. You can specify those statements in Tools->Options->SAS Programs, "Submit SAS code when server is connected." A SAS administrator can also add code to the AUTOEXEC file that runs when the SAS session begins, thus helping to manage this for larger groups of SAS users.
- SAS trick: get the LIBNAME statement to create folders for you
- How to change your working directory for SAS with the DLGCDIR DATA step function
- Find the current directory path in SAS
The post Manage the current directory within your SAS program appeared first on The SAS Dummy.