Top 10 SAS Functions
Did you miss the Ask the Expert session on Top 10 SAS Functions? Not to worry, you can catch it on-demand at your leisure.
Have you ever wondered if there are functions that are popular among SAS users? If so, what are they? Join us as we focus on widely used SAS functions that:
- Manipulate character data (extract, search and concatenate values).
- Work with SAS date and time values.
- Manipulate numeric data (summarize data and round the results).
Here are some highlighted questions from the Q&A segment held at the end of the session for ease of reference.
Why need the third argument on SCAN? fname = scan(name, 2, ‘, ‘);?
The space specifies it as the delimiter.
What if I have 3 fields I want to concatenate, the first and last values may contain blank, the middle has a value. How would I omit the records when the first and third values are blank, but concatenate when the 1st and 3rd have values?
If first=' ' and third=' ' then delete;
Else catx(' ', first, middle, third);
We should mention that the FIND function replaces the INDEX function which abounds in our legacy code.
Yes, the FIND function provides more flexibility than INDEX. Updating legacy code is not an option for everyone, especially when INDEX still works solidly. You may want to use FIND as you develop new code.
Ron Cody recommends the MISSING function which works for character or numeric data.
Yes, thanks for the reminder. We had to curate to 10 functions in order to fit into a 1 hour time constraint.
Shouldn’t we mention Ron Cody's excellent books on SAS functions?
Yes, Ron gives his Functions by Example book to the students in his Functions by Example class https://support.sas.com/edu/schedules.html?id=3005&ctry=US
You don't need a dollar sign on the format when converting data to character with a PUT function?
No, you don't. The format needs to match how the data is stored. PUT always writes character values.
SUBSTR() works based on position, so it's useful if the strings are all the same location. If you knew the character was the last position of a string that could be 4, 5, or 6 characters long, how would you go about it?
You can use SUBSTR to read starting from the right and moving to the left.
When deciding what length value to use when creating a copy of a column of a different data type, what is the best practice?
If the column you are creating is numeric, the storage will default to 8 bytes of floating point notation. If you are creating character data, you should make the length long enough to accommodate the longest name.
So if we want to have a "," between first and last name, we put newname=catx(",",fname,lname). Is that correct?
If you want just the comma to be the separator, then yes, this syntax will work.
How/why was January 1, 1960 chosen?
Great question! See here for more information.
Doesn't the informat in the input statement refer to the format of the character value and to display the new variable as a date you need a separate format statement.
The informat says how you want to read the value that is the first argument in INPUT. The informat has to match how the data is stored. In this situation, we are creating a SAS date, which needs a format to be readable in output.
How does SAS turn US dollars into other currencies? Because those conversions are always changing.
Great question! See here for more information.
Is there an online resource to look up functions and their syntax?
Yes! See here for more information.
How do DATDIF and YRDIF handle leap years? And leap centuries?
Leap years are accounted for within these (and other) functions – that’s one of their advantages.
On transforming IDNO, we specify 4 digits, does that discard all values with 5 digits?
Yes.
I always get INPUT and PUT confused and have to do trial and error to get the right one. Do you have some sort of hint i can use which can be used with numeric and character?
An easy way to remember is PUNCH as a mnemonic - PUT changes numeric to character. PUT always returns a character value; INPUT is the reverse - character to numeric - INPUT is the INverse of PUT. CHIN = character to numeric for input.
When can we use the SCAN function?
The SCAN function can be used when we need to return a word from its relative position. For example, we might want word 1, word 2, etc. The words are delimited by default delimiters or the delimiters of our choice.
Can we think of FIND as a superset of INDEX (to be really quick)?
Yes, you can think of the FIND function as a more powerful version of the INDEX function. Note that FIND allows us to specify a starting position – gives us more control. The FIND function and the INDEX function both search for substrings of characters in a character string; however, the INDEX function does not have the modifiers nor the starting position arguments as FIND provides.
Can we create a custom for date formatting?
Yes. You can use the PICTURE statement in PROC FORMAT. See here and here for two examples.
There are a few places that the values appear to be reformatted, but it does not show up in the code.
Thanks for this constructive comment. We added formats for readability. We are revising the materials to better explain both the stored data value as well as a formatted value for purposes of readability.
How can we use ETL (Extract Transform Load) in SAS? I would like to know about the whole ETL process in SAS. Can you please provide some details about it?
These kinds of functions - plus many others - conduct transformation work. SAS offers many ETL tools and techniques. See here for more information.
How does the SCAN function work if there are multiple consecutive delimiters?
Leading delimiters before the first word in the character string do not effect SCAN. If there are two or more contiguous delimiters, SCAN treats them as one. You can have any number of specified delimiters grouped together, and they are treated as one delimiter.
What is the second word in this as a SCAN statement? given name = 'Stanley, the III'
The answer depends upon how you have specified your delimiters. If your delimiters are blank and comma, then the second word is ‘the’.
Will SAS date values export to Excel workbooks without modification (i.e. subtracting a constant) using 9.3 and above?
If you have a SAS format associated with the date, you should be able to export the file to Excel without modifications. Please see the following for more information.
Did she say that < > means that part of the function is optional?
Yes, <> indicates optional portions of the syntax.
Can I use these functions in SQL?
Most SAS functions are available in the SAS implementation of SQL. Please see the following under the heading SAS DATA step functions for high level information about the types of functions supported.
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. To subscribe, select Subscribe from the Options drop down button above the articles.
