You can use the SOURCE2 system option to get more details in the log for files that are run with %include: options source2; %include 'C:\my SAS files\sample program.sas'; To disable this option: options nosource2; Or, for even more control, you can temporarily enable the option for a specific %include call: %include 'C:\my SAS files\sample program.sas' /source2; Thanks to Otterm1 for sharing this tip             ... View more

If you are a beginner like me and have to work with Excel files, this should help you.   Scenario Suppose you have an Excel file named abc.xlsx that has multiple sheets and let xyz be one of those sheets. Further, suppose that within that sheet you want to extract data from the region B4:H200 and create a SAS data set. This is how you can accomplish this!   proc sql; connect to Excel (path="c:\somedirectory\abc.xlsx"); create table SasDataset as select * from connection to Excel (select * from [xyz$B4:H200]); disconnect from Excel; quit; Note: The code uses column values from the first row to create variable names. See TS-792   Thanks to Mysterious Contributor for sharing this tip. ... View more

A macro variable created with PROC SQL from a numeric variable or function contains leading blanks: proc sql; select count(*) into :howmany from SASHELP.CLASS; quit; %put |&howmany|; | 19|   An easy way to get rid of the leading blanks is as follows: proc sql; select count(*) into :howmany separated by ' ' from SASHELP.CLASS; quit; %put |&howmany|; |19|   Posted with thanks to Robert Allison: Since the original post, the TRIMMED option has been added to the INTO clause in PROC SQL to strip blanks: proc sql; select count(*) into :howmany trimmed from SASHELP.CLASS; quit; %put |&howmany|; |19|    See the documentation for the INTO clause here. Thanks to Mike Zdeb for sharing this tip on sasCommunity.org. ... View more

Call routines SORTN and SORTC can order an array in ascending order only. What if you want to order it in descending order? Here is an easy way!   data _null_; array arr[*] v1-v5 (1 2 3 4 5); put "before:" (v1-v5) (= +1); /* reverse the variable list in a new array definition and sort */ array rev[*] v5-v1; call sortn(of rev[*]); put "after :" (v1-v5) (= +1); run; /* on log before: v1=1 v2=2 v3=3 v4=4 v5=5 after : v1=5 v2=4 v3=3 v4=2 v5=1 */ For a detailed discussion on variable lists, see Using SAS Variable Lists Effectively |Schreier (2011). Thanks to chang_y_chung for sharing this tip on sasCommunity.org. ... View more

Quite often it is necessary to set a default value for a macro variable or parameter that is passed to a program. If the program is a macro, the %IF statement can be used to check if the value is provided and provide a default value if not. Since the %IF statements are not allowed outside of macros, that technique won't work for a non-macro program (e.g., a file/program that is %INCLUDEd). The following code snippet demonstrates the use of the coalescec function to assign a default value. %global myParameter; /* ensure it exists */ %let myParameter = %sysfunc(coalescec(&myParameter,defaultValue)); If the myParameter macro variable has a value, it will be used, otherwise defaultValue will be assigned as the value. This technique can also be used in macros instead of using a %IF statement.   The coalescec function is used regardless of whether the expected value for the parameter is numeric since macro is a text manipulation facility, (i.e., everything in macro is interpreted as a character string). Thanks to Don Henderson for sharing this tip on sasCommunity.org. ... View more

You can use formats to do a quick check for missing or invalid data by creating formats that map the values and then using, for example, PROC FREQ to generate a simple report. In sample the code below, numeric and character formats are created to group the values and then PROC FREQ is used to provide a simple/quick QC report. the special variable list _character_ is used to assign the $missing. format to all the character variables the special variable list _numeric_ is used to assign the missing. format to all the numeric variables proc format; value missing low-high = 'Value' other = 'Missing'; value $missing ' ' = 'Missing' other = 'Value'; run; proc freq data=sashelp.class; tables _all_/missing; format _character_ $missing. _numeric_ missing.; title 'Quick QC Check'; run; Thanks to Don Henderson for sharing this tip on sasCommunity.org. ... View more

Did you know that you can now make your Global and Local macro variables READONLY? New options on the %GLOBAL and %LOCAL statements allow you to not only make variables so that they cannot be inadvertently changed, but you can also assign them initial values.   %global/readonly dsn=clinics; %macro tryit; %local dsn; %* does other stuff; %mend tryit; %* The macro %TRYIT will fail; %tryit The use of these options can help you avoid macro variable collisions, but there are dangers. Once specified as readonly, you can no longer create local versions of this macro variable. This can be a big problem if a called macro also attempts to create another instance of the macro variable - even using the %LOCAL statement.   Thanks to Art Carpenter for sharing this tip on sasCommunity.org. ... View more

A little-known HTML trick is that you can embed images in an HTML file using a Base 64 representation of an image. For example, a folder open image can be generated using the following text in an HTML page: <img src="data:image/png;base64,R0lGODlhEAAQAKL/AP//////AMDAwMDAwICAgICAAAAAAAAAACH5BAEAAAIALAAAAAAQABAAAANFKEpMpjAKAkYYTkJqu2sbII6kSCzApXbqM1VrPLiUWpYGFwBF7/+51ID3KwaHReNrlwS+iM0CTRTtTavSjWHL7W41YE0CADs=" border="0" width="4">   which will display as:    The width parameter can be used to size the image as desired. Note that the image becomes fuzzy if you use a width that is wider than the original image that you encoded.   There are a number of web-sites that will convert images to the Base 64 representation. This Google search provides a sampling.   To see the above img tag as an actual image and for a discussion of a parameter/macro driven approach see my blog post The getImage macro: embedded images using text strings   Thanks to Don Henderson for sharing this tip on sasCommunity.org. ... View more

The INTNX function takes a supplied date, time, or datetime value and can shift it by any specified alignment you choose. For example, if you need to know the first day of the current calendar year, you can use the following formula:   intnx('YEAR', today(), 0, 'B') But what if you need to know the beginning of an odd year format, such as the Federal Fiscal Year? The federal fiscal year starts October 1 and runs through September 30. For example, FFY2010 is October 1, 2009 through September 30, 2010. The optional shift-index portion of the interval argument can shift the interval to start at a specified subperiod starting point. For example, 'YEAR.10' specifies yearly periods shifted to start on the first of October of each calendar year and to end in September of the following year. In other words, to find the first day of the current Federal Fiscal Year, you would use:   intnx('YEAR.10', today(), 0, 'B') Thanks to Tasha Chapman for sharing this tip on sasCommunity.org. ... View more

How often do you use %put NOTE: mvarName=&mvarName; in your SAS code to write out the name and contents of a macro variable?   Since SAS version 9.3, there is a new handy syntax feature that allows you to just do this with less typing.You simple write it as:  %put NOTE: &=mvarName;   For example: %put NOTE: &=sysscpl &=sysvlong; NOTE: SYSSCPL=X64_7PRO SYSVLONG=9.03.01M2P081512     Thanks to Md4 for sharing this tip on sasCommunity.org. ... View more

The title and footnote statement options that were originally supported only in SAS/GRAPH to support changing the height and justification of title and footnote text are now supported in many ODS destinations.   Use the h= (height) and j= (justification) options to do this.   You can see this by running the following code.   ods html file = '\temp.html'; proc print data=sashelp.class; title h=1 'Just' h=2 ' Playing' h=3 ' With' h=4 ' Growing' h=5 ' Titles'; footnote j=left c=blue 'In Blue' j=center c=black 'and' j=right c=red 'And Red'; run; ods html close; Additional options are discussed in the paper SAS/GRAPH Elements You Should Know –Even If You Don’t Use SAS/GRAPH.   Thanks to Don Henderson for sharing this tip on sasCommunity.org. ... View more

Did you know that: the TRANWRD function, which can replace or remove all occurrences of a given word, has a hidden gotcha?   The gotcha is that, if you do not provide a length statement for a new variable that will receive the output from the TRANWRD function, it will default to a length of 200 characters. The inimitable Ron Cody pointed this out in his SUGI 31 paper An Introduction to SAS Character Functions. Here is an example of the gotcha: data _null_; length newstatement1 $34;   statement = "I enjoy going to SUGI conferences."; newstatement1 = tranwrd(statement,"SUGI", "SGF"); newstatement2 = tranwrd(statement,"SUGI", "SGF");   length_newstatement1 = lengthc(newstatement1); length_newstatement2 = lengthc(newstatement2);   put length_newstatement1 = ; put length_newstatement2 = ; run; If you submit the code, above, you will see that newstatement1 ends up with a length of 34, while newstatement2 ends up with a length of 200. That is because newstatement1 had a length statement specifying its size before it was used with the TRANWRD function; while newstatement2 was created fresh and new when used in the TRANWRD function and SAS--not knowing what to make of this infant variable--decided to make it 200 characters in length; the TRANWRD default. So, what is the gotcha? The gotcha is that you could end up with larger variables than you intend to--needlessly inflating the size of a SAS data set--if you are not careful to declare the length of a new SAS character variable that will be created by using the TRANWRD function. You have been warned! Other functions, such as SUBSTR, will also generate long character variables. A secondary issue is that when the returned value exceeds 200 characters, truncation will occur if you have not properly set the length of the new variable. Thanks to MMMMIIIIKKKKEEEE for sharing this tip on sasCommunity.org. ... View more

The ABS function can be used to return a positive value...   DATA POSITIVE_INTEGER; VALUE = abs(-789); RUN; PROC PRINT;RUN;   The SUMABS function is also available, and computes the sum of the absolute value of the non-missing arguments.  This example returns a value of 14.   DATA POSITIVE_INTEGER; VALUE = sumabs(-1, 3, 0, ., -10); RUN; PROC PRINT;RUN; You can also use the OF variable range syntax to include all variables that match a pattern.   data _null_; x1=1; x2=3; x3=4; x4=3; x5=1; x=sumabs(of x1-x5); put x=; run; Thanks to Ayetullah for sharing this tip on sasCommunity.org. ... View more

Use %logitplot(dataset, thePredictorVariable, theOutcomeVariable, NumBins) by Raymond R. Balise (balise@stanford.edu) to test assumptions of linearity; available at : www.stanford.edu/~kcobb/courses/hrp261-->Logit Plot Macro Thanks to Benjamin Knisley, Dr. Joshua Morgan, Dr. Linn Carothers and Keith Haigh for this tip. Read more details in this paper from WUSS 2017: Integrating Health Data Sources to Identify Stratify and Predict High Utilizers of Public Systems. ... View more

Use %SYSEXEC COPY in conjunction with macros and the data step in order to copy selected files (including non-SAS files) from one directory to another. Thanks to Louise Hadden for this tip. Read more details in this paper from WUSS 2017: Get Smart Eliminate Kaos and Stay in Control Creating a Complex Directory Structure with the DLCREATEDIR Statement SAS Macro Language and Control Tables. ... View more