Program Control Depending On Number of Observations

If you have a dataset, the subsequent program's execuation depends on whether this dataset exists or have at least one observation.



%let dsid = %sysfunc(open(demo_ds));

%if &dsid ^= 0 %then %do;
    %let nobs = %sysfunc(attrn(&dsid, nlobs));
    %let rc = %sysfunc(close(&dsid));
%end;

%if &dsid ^= 0 & nobs ^= 0 %then ....

Detect Quoted Macro String

If you have a macro string like this:

%let keyword = "Google";

, you want to determine if this macro string is a quoted string. Below is a handy macro code that does that:


 %if  %qsubstr(&keyword, 1, 1) = %str(%") &
%qsubstr(&grp, %length(&grp), 1) = %str(%") %then
%do;
  %let is_a_quoted_string = Yes;
%end;

Get The Last Day of The Month

Suppose you have a date and want to get the last date of the month of this date. You can accomplish this easily by using INTNX function. Here is the SAS code which does that:


data demo;
format a b mmddyy10.;

a = '02-FEB-2008'd;
/* specifying 'e' will give you the last day of the month */
b = intnx('mon', a, 0,'e');
run;



You can further explore INTNX by reading this paper:
http://www2.sas.com/proceedings/sugi31/027-31.pdf

Convert a Number to a String With Leading Zeros

Suppose you have a list of numbers and want to convert them to a string with leading zeros. Here is the SAS code which does that:

data demo;
input a;

/* convert a number to a 7-character long string*/
b1 = put(a, z7.);

/*use compress function to handle missing values*/
b2 = compress(b1,'.');

/* combine two statements together */
b = compress(put(a, z7.), '.');

datalines;
1234
876
.
; run;

SAS Pass Through Query Interacting With a SAS Dataset

Suppose you have a list of customer account IDs in a SAS dataset and want to find all the customer records of these account IDs in an Oracle table. Since you can't join a SAS dataset with an Oracle table within a pass through query, you have to resort to SAS macro to pass the list into the query. Here is the SAS sample code which shows how to do this:


data acct_id;
input acct_id $;
datalines;
001
002
; run;

data _null_;
set acct_id end = last;
length acct_list $1000.;
retain acct_list;

if _n_ = 1 then acct_list = "'"||trim(acct_id)||"'";
else acct_list = trim(acct_list)||",'"||trim(acct_id)||"'";

/*export the list to a macro variable*/

if last then call symput("acct_list", acct_list);
run;

%put &acct_list;

proc sql;
connect to oracle (user=your_user_id password=your_password);
create table cust_data as
select * from connection to oracle
(
select *
from cust_db
where acct_id in (&acct_list.)
);
disconnect from oracle;
quit;

First Two Digits of NABP/NCPDP Number

The NABP assigns a unique seven-digit code for each licensed pharmacy in the United States. The NABP number is being referred to as the NCPDP pharmacy number. The first two digits indicate state; and the seventh is a “check” digit. Below is the SAS code which generates the mapping of the first two digits of NABP number and state abbreviation code:


data nabp2_state_cd;
input nabp2 $ state_cd $ @@;
datalines;
01 AL 02 AK 03 AZ 04 AR 05 CA
06 CO 07 CT 08 DE 09 DC 10 FL
11 GA 12 HI 13 ID 14 IL 15 IN
16 IA 17 KS 18 KY 19 LA 20 ME
21 MD 22 MA 23 MI 24 MN 25 MS
26 MO 27 MT 28 NE 29 NV 30 NH
31 NJ 32 NM 33 NY 34 NC 35 ND
36 OH 37 OK 38 OR 39 PA 40 PR
41 RI 42 SC 43 SD 44 TN 45 TX
46 UT 47 VT 48 VA 49 WA 50 WV
51 WI 52 WY 55 MP 56 CA
; run;

Finding The Closest String

When you type a wrong keyword in Google, Google will return the result along with the closet string to your keyword. You may wonder if you can do the same thing in SAS. Fortunately, SAS offers a handy function called SPEDIS which can allow you to perform the closest string search. It gives a value measuring the SPElling DIStance between two strings. The lower the value is, the closer two strings are, whereas 0 means two strings are identical. Here is an example. Suppose you want to find LIPITER in your drug database and can't find it, then you realize you may have spelled a wrong brand name and wonder what is the correct one. Below is the SAS code which will return the closest brand name:

data drug_db;
input brand_name $;
cards;
LIPEX
LIPSORB
LIPSTART
LIPITOR
LIPKOTE
LIPMAGIK
LIPMAX
; run;

proc sql outobs = 1;
select brand_name
from drug_db
order by spedis(brand_name,'LIPITER')
; quit;