2 SAS Tips: Subsetting IF and Nth highest salary using Proc SQL

1. Subsetting IF statement without any condition:
(Visitor Contribution:)
data one;
input age;
if age;
cards;
23
12
.
45
0
54
-21
43
;
run;

Output Dataset created:
age
23
12
45
54
-21
43

Conclusion: So, use above IF statement only for FLAG variables which contains values as 0 or 1 (or) . or 1
to have lesser confusion. I mean use it only when you are sure about the data!

2. Extract Nth highest salary from SALARY table using Proc SQL:
data salary;
input sal;
cards;
23
12
.
45
0
54
-21
43
;
run;

%let N=3;
proc sql;
select a.sal from salary a where &N= (select count(distinct sal) from salary b where a.sal<=b.sal);
quit;

Happy Learning

Many SAS BI Tips & Tricks @ …

Many SAS BI Tips & Tricks at:

http://www.bi-notes.com/

How your daily life related to SAS

This is Your Life… with SAS
Your daily activities, morning to night, are influenced by SAS software in ways that you probably never considered. Learn how SAS makes live better in a typical day. Learn more at http://www.sas.com/news/sascom/dayinlife.html

Source: See more such videos at SASsoftware channel on YouTube.

Utility: Drop the Variable if it’s Data is Missing

Drop any variable (Num/Char), if its data is missing in all of the observations…..

%macro DropMissingVars(DS=);
data _null_;
set &ds. (obs=1);
array num_vars[*] _NUMERIC_;
array char_vars[*] _CHARACTER_;
call symputx(‘num_qty’, dim(num_vars));
call symputx(‘char_qty’, dim(char_vars));
run;

data _null_;
set &ds. end=finished;

array num_vars[*] _NUMERIC_;
array char_vars[*] _CHARACTER_;

array num_miss [&num_qty] $ (&num_qty * ‘missing’);
array char_miss [&char_qty] $ (&char_qty * ‘missing’);

length list $ 50;

do i=1 to dim(num_vars);
if num_vars(i) ne . then num_miss(i)=’non-miss’;
end;
do i=1 to dim(char_vars);
if char_vars(i) ne ” then char_miss(i)=’non-miss’;
end;

if finished then do;
do i= 1 to dim(num_vars);
if num_miss(i) = ‘missing’ then list=trim(list)||’ ‘||trim(vname(num_vars(i)));
end;
do i= 1 to dim(char_vars);
if char_miss(i) = ‘missing’ then list=trim(list)||’ ‘||trim(vname(char_vars(i)));
end;
call symput(‘mlist’,list);
end;
run;

data &ds;
set &ds;
drop &mlist;
quit;

%mend DropMissingVars;
%DropMissingVars(DS=work.test);

SAS Keyboard Macros

Keyboard Shortcuts for SAS: to decrease your programming Time….

The Enhanced Editor in SAS allows you to save a series of keystrokes as keyboard macros. A special form of these macros is keyboard abbreviation. The abbreviation allows you to recall stored code with a minimum of keystrokes. In the example that follows, these keystrokes form the outline of a header section of comments for a SAS program.

BUILDING AN ABBREVIATION
By using an abbreviation, we will only need to type this code once. E.g. the following comment is to be
inserted into the top of each SAS program that you write.

/*=====================================================
Project : The project name
Program : 01_client_name_project_purpose.sas
Version : Version Number
Purpose : Brief description of the code and its purpose
Author : Name of the author
Date : Date of creation of the code
Sub Macros : Names of macros if used
Reviewer : Name of the reviewer
Notes : Any notes or remarks about the code
======================================================*/
To add this header as an abbreviation, select the pull down menus Tools and Add Abbreviation.

The Add Abbreviation dialog box is then displayed (see below). In the first dialog space, enter a name for the new abbreviation. This becomes a keyboard macro, so you must select a name that has not already been used. Then type (or more practically paste) the substitution text into the ‘Text to insert for abbreviation’ dialog space and press Ok.

USING THE ABBREVIATION
Whenever you want to use an abbreviation, simply type in the name of the abbreviation while in the Enhanced Editor. As soon as the last letter of the abbreviation has been entered, a small pop-up ‘tip’ text box containing the first few digits of the abbreviation is displayed. If at that point you press the TAB key the name of the abbreviation will be replaced by the text that you stored.
The following screen shot shows that the name of the HEADER abbreviation has been entered in the Enhanced Editor and the first few characters of the text to be substituted is shown in the pop-up ‘tip’ box.

As soon as you press the TAB key the abbreviation name is replaced by the stored text.

Original:http://analysis-reporting.blogspot.com/2011/02/sas-keyboard-macros-and-abbreviations.html

What’s new in SAS Enterprise Guide 4.3

1. Mouse over tooltip – gives you help right in the program editor window.
2. Autocomplete – start typing a keyword, SAS will offer you code options.

Source: http://blogs.sas.com/