SAS MADE EASY

SAS and Machine Learning

2021-08-30T23:34:00.001-07:00

Dear readers

So happy to see SAS entering the AI ML domain and that is a big relief looking at the speed it was getting outdated and noticeable reduction in jobs.

So according to the SAS website, SAS has come up with tools to achieve the following:

Interactive programming in a web-based development environment
Intelligent automation
Natural language generation
Embedded support for Python & R language
Deep learning with Python (DLPy)
SAS^® procedures (PROCs) & CAS actions
Highly scalable, distributed in-memory analytical processing
Model development with modern machine learning algorithms
I am going to review some of these capabilities and will get back to you in further posts. Meanwhile you can also direct me to some of the features you have been using and pros and cons of each.
Our jobs have been made relevant again, Phew..!! :)

Pivot Tables using SAS

2013-12-29T03:37:00.000-08:00

Excel Pivot tables through SAS

Quick Tip

Objective: Hello readers. Pivot table in Excel is an important tool which is used heavily and which makes lot of people’s life much simpler in doing a quick analysis on small data.

It saves the effort to go into SAS create a dataset and start writing code for each scenario. The drag and drop is even more intuitive and easily customizable.

SO I thought of sharing this quick tip to get you started with creating pivot tables directly from your preexisting program which creates the report.

What is a Pivot Table?

I am not a pivot table expert and do not intend to explain Pivot tables here but just for the definition’s sake according to Wikipedia

“In data processing, a pivot table is a data summarization tool found in data visualization programs such as Excel. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data.”

Why use SAS to create one?

Let me tell you one scenario. In SAS reporting mostly we create a final dataset after data summarization which we export to Excel, format it and send to client. But what if your client knows a bit of Excel(which will be the case mostly) he will be happier to get a Pivot table in which he can do a bit of drag and drop to see other metrics for a quick check which you did not deliver(coz you were not supposed to).

Or many times you deliver a few Pivot tables showing different Metrics from the same data.

In both the cases if you automated SAS code creates various Pivots directly without you exporting the data and creating tables manually, all you just have to do is quick checks before sending it forward.

Yes yes I heard that…I know there are many ways to do the same thing. I just liked it, did that on one of my for my boss and he was awed..so I thought maybe you could gain some extra points as well and there is almost no effort involved.

The code:

Ok enough talking, all you have to do is to use this code :

ods path(prepend) work.templat(update);

filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";

%include temp;

ods tagsets.Tableeditor file="%sysfunc(getoption(work))\temp.html"

options(pivotrow="age"

pivotcol="sex"

pivotdata="height,weight"

pivotdata_stats="sum,mean");

proc print data=sashelp.class;

run;

ods tagsets.tableeditor close;

Explanation:

Yes, its that simple, and the code is self explanatory, just replace your variables in Pivotrow= pivotcol= etc., specify the statistics you want on pivotdata_stat= and you are good to go. you’ll get a ODS HTML output with a button. Just click it and the final data is pasted in an excel and a pivot generated.

The URL filename just downloads the tableeditor tagset from SAS website. You can donload and keep it in your local and reference it

Conclusion: This is a clean time saving method to create pivots through SAS. Just give it a try and let me know how did it go in the comments below.

I will delve into SAS and Excel Pivot interface details in a future post.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

An elegant use of call execute-Solution

2013-05-26T02:44:00.001-07:00

An elegant use of call execute

Solution

Objective : Hello readers. This post provides the solution of my previous post. If you have not read that post please go through it to understand the problem that is being solved here.

The previous post can be found here.

The solution:

So my dear readers, 2 days are over and as promised here is the solution.

Now when we have understood the question and the output required, let us look at the solution.

Below is the code I wrote to solve the problem, I will explain it step by step.

/* Macro to create a single account dataset will all transactions of that account */

%macro subset(account_id);

data trans_&account_id.;

set transactions;

where account_id=&account_id.;

run;

%mend;

/* Sorting transaction dataset before By processing */

proc sort data=transactions;

by account_id;

run;

/* For every new account id call the subset macro */

data _null_;

set transactions;

by account_id;

if first.account_id then

call execute('%subset('||account_id||')');

run;

Explanation:

I guess the code is self-explanatory but anyway I’ll explain it step by step.

STEP 1 : The macro subset

This macro contains a simple datastep which just subsets the observation from the complete transaction dataset using a where statement.

For e.g. For account id 111 the code generate with this macro is :

data trans_111;

set transactions;

where account_id=111;

run;

So this code will create a dataset transa_111 and will keep only transaction where account id is 111. This is what is required. So what is left is calling this macro for every account id dynamically as every time the accounts will change. step 3 does just that.

STEP 2 : Sorting

Step 2 is only required because we will be doing by group processing in the next step. So sorting the dataset.

STEP 3 : The call

This step achieves the dynamic call to subset macro using call execute in a datastep. We set the transaction dataset using account id as by variable and whenever the first in group or to say new account is encountered, we use call execute to subset macro passing the current account id as an input parameter. The macro does the rest. Thats it.

Pretty simple right..!!

Conclusion:

This was my way of tackling the question, tell me what is yours in the comments below. Will be very grateful to learn from you folks.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

An elegant use of call execute

2013-05-22T09:05:00.000-07:00

An elegant use of call execute

Objective : Hello readers. This post aims at solving a typical interview question in an elegant manner using call execute. This question is not really tough but can be a tough nut to crack for an uninformed candidate.

Please note that this post is not an explanation of the working of call execute. For that I will write another post sometime in future.

So the questions we are tackling here goes like this :

I have a transaction dataset which stores all the transactions done by all the bank’s customers in the past month. Now I want you to create a separate dataset for each account id which should contain all the transaction done for that account.

For. E.g. I have 5 account ids and the transaction dataset contains all the 300 transactions done for these 5 accounts. So as final output I want 5 datasets by the name of the account ids and each of those dataset must contains all the transactions for just that account id.

The data:

We will be using the following dummy data for the exercise:

For your convenience here is the code to create that:

data transactions;

input account_id trans_id amount;

datalines;

111 1 250

111 2 567

111 3 7890

222 1 2250

222 2 578

333 1 8965

444 1 74216

444 2 154

555 1 4879

;

run;

The output:

There are 5 different accounts in the data, so the output will be 5 datasets(data_111, data_222,...) and each dataset will contain its own transactions.

For e.g. Data_111 will have the following 3 observations:

111 1 250

111 2 567

111 3 7890

ALERT..!!!

Why don’t you try solving it yourself before looking at the solution. It’s not mandatory to use call execute, there are 10 different ways.

By trying it You’ll get to know the difficulty and will be able to appreciate the elegance of the solution more so.

The solution:

I will not post the solution right away, please try it yourself and post your answers in comments below. Maybe you will have an even better code to solve it and I can learn from your code. I will post my solution in 2 days.

The solution can be found here (Link coming up in 2 days)

Conclusion:

Please post your solution as comments so that I can learn from you experts.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

How to create a Cartesian product using a datastep ?

2013-05-01T07:17:00.000-07:00

How to create a Cartesian product using a datastep ?

Objective : Hello readers. This post is unlike all my previous posts which deal with one or the other concepts of SAS and explain them in detail. The idea for this post came up from one of the interview questions which I have been asked many times over (and I am asking it now when I am on the other side of the table :-) ).

In one of my previous posts I have compared the datastep merges with SQL joins, but no one ever tells how to do in datastep what is called a Cartesian product in SQL, and which is pretty easy to do in SQL. Interestingly Cartesian product forms tha base of SQl joins, so if we can replicate SQL joins in datastep then we should also be able to replicate Cartesian product..!!

The answer is Yes we can do it, and easily. But you’ll know it only when you see it.

So here is the short and simple way to do it, but before that let me explain for people who don’t know about Cartesian product, what it is and what is a point option(Shhh..this is the key)

Cartesian product:

This is a mathematical term which is used in set theory. A Cartesian product of set A and set B is a set of all ordered pairs of the elements of A and B.

That was complicated..!! Thank GOD we don’t have to pass a mathematics paper anymore.

Ok, forget maths, in SAS when we do a Cartesian product of dataset A and dataset B, then we create a dataset containing all rows of A horizontally combined with all rows of B.

So if A has 3 observations and B has 4, Then the Cartesian product will have 12 observation and all the unique columns of A and B.

In SQL we do a Cartesian product similar to a join but just eliminating giving any join names or or joining conditions. Here is the syntax:

Proc sql;

create table cartesian1 as

select * from cart1,cart2;

Quit;

Simple right..!!

Though it is not this simple in datastep and that is why I never got correct answer from any of the interviewees till date. Actually the reason is they have never seen it before.

Also using this technique requires a knowledge of point option in SAS datastep. Here is a brief overview of the option:

Point option:

The point creates a temporary variable whose numeric value determines which observation is read in a set statement. POINT= can cause the SET statement to use random (direct) access to read a SAS data set instead of sequential. Here is an example:

data test;

do i=6,11,16;

set sashelp.class point=i;

output;

end;

stop;

run;

The point option is used to directly read observation number 6,11 and 16 from another dataset without reading any other observation. This is a dumb example but this option can be put to a lot of good uses and clever and efficient coding. We’ll talk about it later.

Cartesian product using a datastep:

Below is the code that will create the datasets I will use for Cartesian product and the third step will create a Cartesian product exactly the same as we created using proc sql above.

data cart1;

input id name $;

datalines;

1 test1

2 test2

3 test3

;

run;

data cart2;

input grp1 $ grp2 $;

datalines;

xx pp

yy qq

zz rr

aa ss

;

run;

/* Creates cartesian product */

data cartesian;

set cart1;

do i=1 to 4;

set cart2 point=i;

output;

end;

run;

The third step creates a Cartesian product and if you read this post from start, you won’t need me to explain the functioning.

Going flexible:

As one of my friends on linkedin pointed out, that there is some hardcoding in this step owing to which the code is losing its flexibility. I have mentioned the number of observations(4) in the loop, which makes the above step not easy to use as you need to first get the observation in the second dataset and then change the number in the code.
We know that SAS can count the number of observation in a dataset, so why not let SAS do the counting while we are having a coffee..!!

The code to do this will use nobs option as follows:

data cartesian;
set cart1;
do i=1 to totobs;
set cart2 point=i nobs=totobs;
output;
end;
run;

So we get the final code to do cartesian product and it uses just 7 lines..!!

Conclusion:Frankly speaking Creating a cartesian product using a datastep is not a very elegant technique, Just read this post for the sake of answering that final interview question which will seal your dream job. When it comes to creating a Cartesian product in code just use proc sql which is much cleaner and easier to understand.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS Date and Time functions(Part 2)

2013-04-22T21:10:00.001-07:00

SAS Date and Time functions(Part 2)

A quick reference

Objective : This post is the second in the series of posts on functions and Second of two posts on Date Time functions. The first post was on SAS character functions. This post highlights frequently used SAS Date and time functions and provides a quick reference list and is invaluable for revision before interviews. Also this posts aim to strengthen the your function knowledge by providing interesting examples.

Date time functions:

MDY Function:

This function takes month, day and year as numeric values and returns a SAS date value created from them.

Syntax:

MDY(month,day,year)

E.g.

data _null_;

sasdt1=MDY(01,31,2009);

format sasdt1 date9.;

put sasdt1=;

run;

LOG:

sasdt1=31JAN2009

YEAR Function:

This function extracts the year from a SAS date value.

Syntax:

YEAR(date)

Date – Any SAS date value

E.g.

data _null_;

ye=YEAR('31Jan2011'd);

put ye=;

run;

LOG:

ye=2011

QTR Function:

This function determines which quarter of the year, the date in the argument lies into and return the number of that Quarter.

Syntax:

QTR(date)

Date – Any SAS date value

E.g.

data _null_;

qtr=QTR('31Jan2011'd);

put qtr=;

run;

LOG:

qtr=1

MONTH Function:

This function extracts and returns the Month value from a SAS Date value.

Syntax:

MONTH(date)

Date : Any SAS date value

E.g.

data _null_;

x=MONTH('04APR2013'd);

put x=;

run;

LOG:

x=4

WEEKDAY Function:

The weekday function returns and integer corresponding to the day of the week which is on the date passed as argument. With Sunday denoted as 1, Monday as 2 and so on.

Syntax:

WEEKDAY(date)

Date : Any SAS date value

E.g.

data _null_;

x=WEEKDAY('04APR2013'd);

put x=;

run;

LOG:

x=5

DAY Function:

The day function returns and integer donating the day of the month which is in the date passed as argument.

Syntax:

DAY(date)

Date : Any SAS date value

E.g.

data _null_;

x=day('04APR2013'd);

put x=;

run;

LOG:

x=4

HOUR Function:

The HOUR function accepts a SAS Date value or Datetime value as an argument and extracts and returns the hour value .

Syntax:

HOUR(<time | datetime>)

Time | Datetime – Any SAS Time or Datetime value

E.g.

data _null_;

hr=HOUR('31Jan2011:10:26:56'dt);

put hr=;

run;

LOG:

hr=10

MINUTE Function:

This function extracts the Minute value from a SAS Time or Datetime value.

Syntax:

MINUTE(time | datetime)

Time | Datetime – Any SAS Time or Datetime value

E.g.

data _null_;

min=MINUTE('31Jan2011:00:26:56'dt);

put min=;

run;

LOG:

min=26

SECOND Function:

This function extracts the Second value from a SAS Time or Datetime value.

Syntax:

SECOND(time | datetime)

Time | Datetime – Any SAS Time or Datetime value

E.g.

data _null_;

sec=MINUTE('31Jan2011:00:26:56'dt);

put sec=;

run;

LOG:

sec=56

INTCK Function:

This function is one of the most important date functions and is also asked heavily in SAS interviews.

Intck function calculates the number of interval(Year, Month,day etc.) boundaries between two dates, the from and To dates.

Syntax:

INTCK('interval',from,to)

Interval -

The intervals that can be used are :

DAY, WEEK, WEEKDAY, TENDAY, SEMIMONTH, MONTH, QTR, SEMIYEAR, YEAR, HOUR, MINUTE, SECOND.

From – The SAS start date value

To – The SAS end date value.

e.g

data _null_;

date1='1oct1993'd;

date2='1oct2003'd;

newyears=intck('year',date1,date2);

put newyears=;

run;

LOG:

newyears=10

INTNX Function:

This function is one of the most important date functions and is also asked heavily in SAS interviews.

Intnx function increments the strat-date by number of intervals(Year, Month,day etc.) specified by increment.

Syntax:

INTNX('interval',start-date,increment<,'alignment'>)

Interval -

The intervals that can be used are :

DAY, WEEK, WEEKDAY, TENDAY, SEMIMONTH, MONTH, QTR, SEMIYEAR, YEAR, HOUR, MINUTE, SECOND.

start-date – The SAS start date value for the increment

increment – The number of increments for which the date has to be incrmented.

Alignment - controls the position of incremented date within the interval. Alignment can take the following values:

BEGINNING(Alias: B) : This specifies that the date that is returned is aligned to the beginning of the interval.

MIDDLE(Alias: M): This specifies that the date that is returned is aligned to the midpoint of the interval, which is the average of the beginning and ending alignment values.

END(Alias: E): This specifies that the date that is returned is aligned to the end of the interval.

SAME(Alias: S): This specifies that the date that is returned has the same alignment as the input date.

e.g

data _null_;

date1='1oct1993'd;

final_date=intnx('year',date1,10,'B');

format final_date date9.;

put final_date =;

run;

LOG:

final_date=01JAN2003

The year has been incremented by 10 Years and the date is aligned to the beginning of the YEAR interval which is 1st Jan.

Conclusion: I hope this post will help you to improve your knowledge on functions and also will serve as a quick reference while preparing for interviews.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS Character functions(Part 2)

2013-04-08T11:16:00.002-07:00

SAS Character functions(Part 2)

A quick reference

Objective : This article aims at creating a one stop solution for all SAS character functions with explanation and usage examples. This is the Second of the two posts on SAS Character functions. The first post can be found here:
SAS Character function Part 1.
Other posts on functions can be found below:
SAS Date Time Functions(Part 1)

Theory : SAS functions are one of the most useful constructs designed to make our life simple and easy. Here I will try to cover all frequently used SAS character functions with examples. You can also define your own functions and subroutines but that is a separate topic in itself and I will discuss it in a separate post.

Below is the alphabetic list of character functions I have used with a simple examples and usage notes. You can just scroll through to find the function you are searching for.

Function	Usage	Syntax	Function Example	Returns
FIRST	To extract first character from a string	FIRST(string)	FIRST("Saurabh")	S
INDEX	To locate the starting position of a substring in a string.	INDEX(String,Search string)	INDEX("being human","human")	7
INDEXC	To search a character string for one or more characters	INDEXC(String,character list)	INDEX("being human","abc")	1
LEFT	Left aligns character values by moving leading blanks to the end of string	LEFT(string)	LEFT(" Hello")	Hello
LENGTH	Returns the length of a string, not counting trailing balnks	LENGTH(String)	LENGTH("ABC ")	3
LENGTHC	Returns the length of a string, counting trailing balnks	LENGTHC(String)	LENGTHC("ABC ")	6
LENGTHM	Returns the length of a string, in memory	LENGTH(String)	LENGTHM(" ")	1
LOWCASE	Converts all characters to Lowercase	LOWCASE(String)	LOWCASE("HeLLO")	hello
MISSING	Used ti determine whether the argument's value is missing. Returns 0 for non-missing otherwise 1	MISSING(String)	MISSING(" ")	1
NOTALNUM	Searches for the first occurrence of an Alphanumeric character in a string.	ANYALNUM(String <,startpos>)	Anyalnum("%$#sa1",2)	2
NOTALNUM	Second argument gives the position to start the search.	ANYALNUM(String <,startpos>)	Anyalnum("%$#sa1",2)	2
NOTALPHA	To determine the position of the first character in a string that is not an alphanumeric.	NOTALPHA(String <,startpos>)	notalpha("%$#sa1",1)	1
NOTDIGIT	To determine the position of the first character in a string that is not a numeric.	NOTDIGIT(String <,startpos>)	notdigit("%$#sa1",1)	1
NOTLOWER	To determine the position of the first character in a string that is not a lowercase character.	NOTLOWER(String <,startpos>)	notlower("%$#sa1",1)	1
NOTUPPER	To determine the position of the first character in a string that is not an alphanumeric.	NOTUPPER(String <,startpos>)	Anypunct("%$#sa1",1)	1
PROPCASE	Capitalizes first letter of each word in a string	PROPCASE(String)	Propcase("indira gandhi")	Indira Gandhi
RANK	To give ranks or obtain relative position of ASCII characters	RANK(char)	RANK("A")	65
REPEAT	Repeats a given string. Result is the original string plus repapeated ones	REPEAT(String,number of times)	REPEAT("Hi",3)	HiHiHiHi
REVERSE	Reverses a given string	REVERSE(String)	REVERSE("HELLO")	OLLEH
RIGHT	Right aligns a string by putting trailing blanks at beginning	RIGHT(String)	RIGHT("ABC ")	" ABC"
SCAN	Returns the nth word from a string	SCAN(String,number)	SCAN("how are you",3)	you
STRIP	Removes leading as well as trailing blanks	STRIP(string)	STRIP(" ABC ")	"ABC"
SUBSTR	Extracts a part of a string	SUBSTR(String,startpos<,length>)	SUBSTR("mohit",2,3)	ohi
TRANSLATE	Changes one character value to another. Lists can be passed and the character will be replaced by their corresponding characters	TRANSLATE(String,to_list,from_ list)	TRANSLATE("mr india","xy","mi")	xr yndya
TRANWRD	Replaces one word by other	TRANWRD(String, find , replace)	TRANWRD("how are you","how","who")	who are you
TRIM	Removes trailing blanks	Trim(string)	TRIM("ABC ")	"ABC"
UPCASE	Converts all characters to Upppercase	UPCASE(String)	UPCASE("Hello")	"HELLO"

Conclusion : This and the previous post is not an exhaustive list of all SAS Character functions available but it covers almost all functions you’ll use in day to day job

And a alphabetical list like this can act as your quick reference or you can even print and stick it on your desk for super quick reference.

I have tried my best to eliminate erros but if any please write it as a comment. Suggestion are always welcome.

Will be back with some more topics. Till then goodbye.

Saurabh Singh Chauhan
(er.chauhansaurabh@gmail.com)

Note : If you need an article on a specific topic please post as comment.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS Date and Time functions(Part 1)

2013-04-04T08:20:00.000-07:00

SAS Date and Time functions(Part 1)

A quick reference

Objective : This post is the second in the series of posts on functions and first of two posts on Date Time functions. The first post was on SAS character functions. This post highlights frequently used SAS Date and time functions and provides a quick reference list and is invaluable for revision before interviews. Also this posts aim to strengthen the your function knowledge by providing interesting examples.
All the SAS function posts can be found here:
SAS Character function Part 1
SAS Character function Part 2

SAS Date and Time functions Part 1
SAS Date and Time functions Part 2

Date time functions:

DATDIF Function:

This function outputs the number of days between two dates, the start and end date. The calculation is done based on the value of the third argument(basis).

Syntax :

DATDIF(Start_date, End_date, basis)

Start_date : The period start date.

End_date : The period end date.

Basis : Basic can take the following values :

ACT/ACT – The actual number of days are calculated between two dates.

30/360 – Calculations are based on 30 day month and 360 day year.

ACT/360 – Calculations are based on ACT days in months but 360 days in a year.

ACT/365 - Calculations are based on ACT days in months but 365 days in a year.

E.g.

data _null_;

stdate='01jan10'd;

eddate='15jul13'd;

actual_days=datdif(stdate, eddate, 'act/act');

days_360=datdif(stdate, eddate, '30/360');

put actual_days= days_360=;

run;

Log: actual_days=1291 days_360=1274

YRDIF Function:

This function outputs the number of year elapsed between two dates, the start and end date. The calculation is done based on the value of the third argument(basis).

Syntax :

YRDIF(Start_date, End_date, basis)

Start_date : The period start date.

End_date : The period end date.

Basis : Basic can take the following values :

ACT/ACT – The actual number of days are calculated between two dates.

30/360 – Calculations are based on 30 day month and 360 day year.

ACT/360 – Calculations are based on ACT days in months but 360 days in a year.

ACT/365 - Calculations are based on ACT days in months but 365 days in a year.

E.g.

data _null_;

stdate='01jan1930'd;

eddate='15jul1999'd;

actual_years =YRDIF(stdate, eddate, 'act/act');

years_360 =YRDIF(stdate, eddate, '30/360' );

put actual_years= years_360=;

run;

Log: actual_years=69.534246575 years_360=69.538888889

As you would expect, the number of years increased when we counted a 360 day Year.

DATE Function:

This function is equivalent to TODAY() function as it returns the current date value. This function is used for codes which are run daily to dynamically get the current date.

Syntax:

Date()

E.g.

data _null_;

format x date9.;

x=DATE();

put x=;

run;

Log: x=03APR2013

TIME Function:

This function returns the current time value.

Syntax:

Time()

E.g.

data _null_;

format x Time8.;

x=TIME();

put x=;

run;

Log: x=16:47:52

TODAY Function:

This function is equivalent to DATE() function as it returns the current date value. This function does not accept any argument. This function is used for codes which are run daily to dynamically get the current date.

Syntax:

Today()

E.g.

data _null_;

format x date9.;

x=today();

put x=;

run;

Log: x=03APR2013

DATEPART Function:

This function extracts just the date and leaves out the Time part from a SAS Datetime value.

Syntax :

DATEPART(datetime)

Datetime : Any SAS datetime value

E.g.

data _null_;

format x datetime20.;

format y date9.;

x=datetime();

y=datepart(x);

put x=;

put y=;

run;

LOG:

x=03APR2013:23:10:23

y=03APR2013

TIMEPART Function:

This function extracts just the time and leaves out the date part from a SAS Datetime value.

Syntax:

TIMEPART(datetime)

Datetime - Any SAS datetime value

E.g.

data _null_;

format x datetime20.;

format y date9.;

x=datetime();

y=timepart(x);

put x=;

put y=;

run;

LOG:

x=03APR2013:23:10:23

y=23:10:23

DATETIME Function:

This function returns the current date and time as a SAS Datetime value. It does not take any arguments.

Syntax:

DATETIME()

E.g.

data _null_;

format x datetime20.;

x=datetime();

put x=;

run;

LOG:

x=03APR2013:23:24:11

DHMS Function:

This function is used to create a SAS Datetime value from a SAS date value and Hour, Min and Second values.

Syntax:

DHMS(date,hour,minute,second)

E.g.

data _null_;

x=DHMS('04APR2013'd,12,10,45);

format x datetime20.;

put x=;

run;

LOG:

x=04APR2013:12:10:45

HMS Function:

This function takes Hour,Minute and Second numeric values as arguments and returns a SAS Time value.

Syntax:

HMS(hour,min,sec)

E.g.

data _null_;

x=HMS(12,10,45);

format x time8.;

put x=;

run;

LOG:

x=12:10:45

JULDATE Function:

This function returns a Julian date value taking a SAS Date value as an argument.

Note : A Julian date is notation where a year is represented as first 2 digits(If the date falls between the YEARCUTOFF option boundaries) or first 4 digits and next 3 digits as the number of days elapsed In an year.

For e.g. 10Jan1911 will be represented as 1911010 and 10Jan1935 as 35010.

Syntax:

JULDATE(date)

Date- Any SAS date value

E.g.

data _null_;

juldt1=juldate('30dec2099'd);

juldt2=juldate('30dec2011'd);;

put juldt1= juldt2=;

run;

LOG:

juldt1=2099364 juldt2=11364

JULDATE7 Function:

Same as JULDATE but always returns a 4 digit year. Recommended to use as output lengths do not vary with dates.

DATEJUL Function:

This function is the opposite of JULDATE function. It takes a Julian date value as input and returns a SAS date value as output.

Syntax:

DATEJUL(Julian_date)

Julian_date – A Julian date value.

E.g.

data _null_;

sasdt1=datejul(2099101);

format sasdt1 date9.;

put sasdt1=;

run;

LOG:

sasdt1=11APR2099

Conclusion: I hope this post will help you to improve your knowledge on functions and also will serve as a quick reference while preparing for interviews.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

Differences in UPADTE and MODIFY statements in SAS

2013-03-24T02:03:00.000-07:00

Difference in UPDATE and MODIFY statements in SAS

Intoduction :

Update and Modify are two underused statements in SAS programming language, but the questions regarding the working of each and the differences between them is a heavily asked question in SAS interviews, the latter one being more frequent.

This post sheds some light on working of update and modify with extra stress on comparing them and listing out the differences.

This post has enough information to get you through most of the interview questions, but is not an exhaustive material on UPDATE and MODIFY statements. For complete working of these please visit SAS website.

So let us start with explanation of each and then we’ll see the differences:

What is Updating:

Updating a SAS dataset means replacing the variable values in one data (typically called the master dataset) with values from another data set (the transaction data set).

If optionUPDATEMODE=MISSINGCHECK, then blankvalues in a transaction dataset will not replace current values in a master data set.
If option UPDATEMODE=NOMISSINGCHECK, then blank values in transaction dataset replace preexisting values in the master data set.
By default it is set to MISSINGCHECK.

You update a data set by using the UPDATE statement along with a BY statement. Both of the input data sets must be sorted by the variable that you use in the BY statement. The following figure shows the results of updating a SAS data set.

As you can see the names for accounts numbers 6 and 8 are updated according to the transaction dataset.

Also a new account (Acc no. 9) has been added to the master dataset.

data master;

input acc_no first_name $ last_name $;

datalines;

1 A B

2 A B

3 A B

4 A B

5 A B

6 A B

7 A B

8 A B

;

run;

data transaction;

input acc_no first_name $ last_name $;

datalines;

6 C D

8 C D

9 C D

;

run;

data master;

update master transaction;

by acc_no;

run;

So think of the places where you’ll require an UPDATE. One place is a company dataset which stores the salaries of the employees. Every month the salaries are updated for existing employees and new joinees and their salaries are appended to the existing dataset.

Definition of Modifying:

When we MODIFY a SAS data set we replace observations or parts of them in an existing data set. Modifying a SAS data set is similar to updating a SAS data set, but the following differences exist:

Modifying cannot create a new data set, while updating can. Though it is actually not a limitation as it results in less disk space being used.

Unlike updating, modifying does not require that the master data set or the transaction data set be sorted.

We can change an existing dataset by using the MODIFY statement with a BY statement. The following figure shows the results.

The code used for this is :

data master;

input acc_no first_name $ last_name $;

datalines;

1 A B

2 A B

3 A B

4 A B

5 A B

6 A B

7 A B

8 A B

;

run;

data transaction;

input acc_no first_name $ last_name $;

datalines;

6 C D

8 C D

;

run;

data master;

modify master transaction;

by acc_no;

run;

If we try to add a new value using MODIFY statement with BY statement we get an error as MODIFY searches a match for all observations and if a match for any observations in the transaction dataset is not found, it thoriws an error.

See the example below:

data transaction;

input acc_no first_name $ last_name $;

datalines;

6 C D

8 C D

9 C D

;

run;

data master;

modify master transaction;

by acc_no;

run;

ERROR: The TRANSACTION data set observation does not exist on the MASTER data set.

ERROR: No matching observation was found in MASTER data set.

Differences:

MODIFY statement, has overcome the limitations of SET, MERGE, and UPDATE statements.
For eg, this statement:

Can be used to update a dataset in place(Does not make a copy)
Can use sequential, matching, or direct access method.
Sorting is not required unlike MERGE and UPDATE while match merging.
Unlike Merge and UPDATE does not give an error if duplicate BY values exist in master or transaction dataset or both.
Requires less disk space than UPDATE, MERGE etc. as a temporary copy of master dataset is not made.

The MODIFY statement can only be used to update values of existing data set variables; no changes can be made with respect to the structure of the program data vector that is created at execution time for the data set being modified.

The syntax for MODIFY and update are similar when performing matching access.
In both the master dataset is mentioned followed by the transaction dataset. Both require a BY statement which lists the variables whose values from the trans. dataset are used to find observations in the master data set. However, the execution processing for both statements is not same.
Written below is how observations for update are retrieved in matching access. During execution,

First the MODIFY statement pick up a row from the trans.n dataset;
Secondly, MODIFY generates a WHERE condition, which specifies the value of the BY variable from the row of transaction dataset , to find and get a row from the master data set.

The following rules always apply when duplicate values exist:

If there are multiple observations with the same value for the BY variable in the master data set, MODIFY changes only the values for the first occurrence. For UPDATE statement is that processing does not continue if duplicate values of the BY variable are detected in the master data set.
If there are multiple observations with the same value for the BY variable in the transaction data set, MODIFY performs operations consecutively, a manner simi1ar to that of the UPDATE statement.

I found the table below on SAS website, which summarizes the differences between MERGE, MODIFY and UPADTE :

Comparing Modifying, Merging, and Updating Data Sets

Comparison	MERGE	UPDATE	MODIFY
Is sorting of datasets required?	Merge with By statement: Required One on one merge: Not required	Required	Not required
Requirement of Unique By values	Not required	Master data set: Required Transaction data set: Not required	Not required
Can variables be created or deleted	Can be created	Can be created	Cannot be created
Number of dsets that can be combined	Unlimited	Two	Two
Missing value handling	Non-missing values are overwritten	If there are missing values in transaction dataset the values in the maser are not updated	If UPDATEMODE option is MISSINGCHECK then does not overwrite non-missing else overwrite. Default: MISSINGCHECK

Conclusion: Update and Modify have a lot of things in common but have very specific differences and that is why this question comes up again and again in interviews. Hope this post will help you get the hang of these useful statements and gain you confidence.

Will be back with some more SAS magic. Goodbye till then.

References :

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001303248.htm

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

Arrays in SAS

2013-02-28T01:03:00.000-08:00

Arrays in SAS

You must have seen arrays being used in various programming languages and must already know quite a bit about them, but before we go into the details let me tell you one thing :

ARRAYS IN SAS ARE DIFFERENT FROM ANY OTHER LANGUAGE..!!

In most of the languages array is a data structure, holding data values, but in SAS it is not a data structure, it is just a collective name given to a group of variables. Being clear with this distinction is very integral to the part of using arrays successfully in SAS.

In SAS the most important function of the array is to reduce the lines of code where a programs involves repetitive calculations on different variables. I have seen most programmers shy away from their use, but let me tell you that it is a wonderful tool to make your code simple. Also in some instances arrays provide flexibility to make your code dynamic, I’ll get back to this later.

So let us get down to the dirty details :

What are arrays?

An Array is a grouping of variables of the same type used to perform repetitive operations on those variables.

Syntax:

Array array_name[dimension] $ length variable-list

Array – The ARRAY keyword for decleration

Array_name – Any valid SAS name (Do not use function names)

Dimension – Number of elements in the array(If unknown can use * but then we have to provide the varable list)

$ - Tells that the array is character array.

Length – Length of each variable

Variable list – List of variables to be part of the array(Can use named ranges)

Usage:

We have a dataset with 10 variables(kg1 to kg10) containing weights of a patients measure for 10 consecutive weeks. We want to convert these weight to pounds. One way we can do it is :

data kg_to_lbs;

set weights;

lbs1=kg1*2.2;

lbs2=kg2*2.2;

lbs3=kg3*2.2;

...

lbs10=kg10*2.2;

run;

But this generates 13 lines of code for one simple calculation. Imagine when we have 100 such measurements, So here we can use arrays to make code shorter and easy to understand:

data kg_to_lbs;

set weights;

array kg_array {10} kg1-kg10;

array lbs_array {10} lbs1-lbs10;

do i = 1 to 10;

lbs_array{i} = (kg_array{i})*2.2;

end;

run;

cool na..and there is nothing complicated about this, you just need to declare an array and use a simple do loop.

Below are a few points about arrays which will be all you need to know about them to utilize arrays to their full potential.

Important points to note about arrays:

1) Variables used in an array must be of the same type. Either all numeric or all character.

2) Variables need not be preexisting variables, if they does not exist then SAS creates them for you. This converts into a useful application of creating variables through arrays.

3) SAS needs to know the size(number of elements) of the array while you are creating it. You can supply the size in brackets next to the array name

Array test[10] $5;

Any array of 10 elements

Or you can let SAS count them for you using the number of variables in the variable list.

Array test1[*] $ var1 – var5

You cannot emit both the dimension and variable list together.

4) Array does not accept numeric variables in the brackets whose value may define the dimension because it creates the array in compile time and the value of the variable used in brackets will be available only in compile time.

Array test[num_var];

ERROR : Array requires a numeric constant

5) If we want to use all numeric or all character variables of a dataset without bothering about their names then you can declare the array like :

Array nums[*] _NUMERIC_;

Array nums[*] _CHARACTER_;

6) Sometimes we need an array to hold values temporarily but do not want to output those variables to the final dataset then we can use temporary arrays. They are declare as :

Array arr_name[10] _TEMPORARY_;

Functions used with arrays :

DIM FUNCTION :

This function helps to determine the number of elements in a array dynamically so while looping you do not have to hardcode it.

For e.g.

Do i=1 to 10;

A[i]= b[i] + c[i]

End;

Can also be written as :

Do i=1 to dim(a);

A[i]= b[i] + c[i]

End;

VNAME FUNCTION :

We get the values of variables in an array using the array name and subscript but if we want the name of the element(variable) by its subscript then we can use the vname function. For e.g.

array arr_name[*] X Y Z P Q R;

i=3;

vars = Vname(arr_name [i]);

So vars will be initialized to the name of the third variable in the list which is Z.

OF OPERATOR :

This operator is very useful when we have to perform and operation on all elements(or variables) of an array. For e.g

We need a sum of all elements of an array we can write :

X=sum(a[1],a[2],a[3],a[4],a[5],a[6],a[7]…)

But if we don’t know how many elements are there or it changes every time then you need to update it again and again, so instead we can write

X=sum(of a[*]);

Cool and easy.. :-)

Here is the SAS page if you need to go further and read more about arrays :

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201956.htm

Conclusion: So after reading this paper I hope readers will be able to utilize arrays in their code for flexibility and more structured programs.

That’s it.

Will be back with some more SAS magic. Goodbye Till then and keep learning.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies.

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

Scheduling SAS jobs using windows scheduler

2012-05-27T03:01:00.000-07:00

Windows scheduler to run SAS programs

For PC SAS installation

Objective: The objective of this post is to give the readers running periodic jobs an initial introduction on scheduling jobs using windows scheduler and make their lives easy by making the jobs run without any human intervention.

What is a windows scheduler?

A windows scheduler is a facility in windows which can be used to schedule various jobs (not only SAS but almost any application installed on the system) to run at specific times or to run periodically at the dates mentioned.

It has a wizard for scheduling jobs which is very intuitive and you’ll not need any help even when you are using it the first time.

Step by step process to schedule a job?

So here I will be discussing a step by step process which you can follow to schedule a SAS job. Of course there a multiple other ways which will be discussed in separate posts. This process will take you through the process with visual so that you can go ahed and schedule the job on your own. Then we can discuss certain specifics :

STEP 1) Create a batch file:

A batch file is a file which can be used execute certain jobs. Though the names looks intimidating, it is very easy to create one.

To create a batch file used to run one or many SAS jobs, open notepad, type the path of the SAS software then – SYSIN and then the path of the SAS program which you want to execute using windows scheduler.

Your file content will look something like this :

“C:\Program Files\SAS\SASFoundation\9.2(32-bit)” –SYSIN “C:\my_progs\codes\mysasprog.sas”

For multiple programs you will have multiple rows like this with the path of the code changed.

The path of SAS software can be different for different installations.

To find it : Go to SAS icon -> right Click -> Properties -> Shortcut tab -> open file location

This will take you to respective folder and you can copy and paste the address.

Path of program you’ll know as you have written it J

SYSIN option specifies to start SAS and submit the file in batch mode.

When you file is complete save it as ABC.BAT or you can rename a .txt file to .BAT also.

SO now you have your batch file.

STEP 2) Schedule the job using windows scheduler:

Go to Start -> Control panel -> Scheduled tasks (In windows NT and Xp)

For windows 7 the process is same only the look and feel is different.

Go to Start -> Control panel -> Administrative tools -> Task scheduler.(Windows 7)

I’ll tell the process further for windows XP for windows 7 also it is the same with a different look and feel but it is intuitive.

Double click on ADD SCHEDULED TASK

A window will open with a list of software, no need to do anything with the list, just click browse and specify you batch file in it.

Press next, it will ask you for timings when you want to run the task. You can specify something like Weekdays the a list will open for selecting which weekdays you want to run or else you can just select if it is an everyday task and has to be run on weekends as well.

Press next, it will ask for your windows logon password. It will be used when your system is locked to run the job. Just give the task and press FINISH.

And WOW !! your task is scheduled. It will run at specified times without your intervention.

STEP 3) Points to keep in mind:

1) Your program should be self sufficient. That means when you generally run that program you just press F3 and do nothing else like changes in code etc.

The reason being, that if the job requires human intervention then the original purpose of scheduling is defeated.

IF your program is not self sufficient the make it..SAS is powerful !!

Taking a simple example, you have to change the date in the program to today date before running it, the use today function and make the date selection dynamic.

If you have to do some formatting in the excel sheet your programs generates before sending mail, create a permanent VB macro and call it in the program which automatically does the formatting.

If you have to send the results generated in the program by mail, use SAS mailing facility (you can also send attachments)

The only point is if you are not in office on a particular day the program should still do the complete job.

2) Use proc printto to save logs and mailing facility to send those to you through mail so that you can check them directly without even going to the job location.

3) TO run the jobs consecutively you just have to specify multiple rows in the file as stated above.

But if the programs are independent and can be run concurrently then each row in your batch file should start with

Start “Job1” “C:\Program Files\S… <same as above>

Start “Job2” “C:\Program Files\S… <same as above>

Job1 and Job2 are just any names and are required in the syntax. SO you can use anything, no dependencies.

The above syntax in your batch file will run your jobs concurrently.

Further reading:

The below paper I found on net describes the same process with graphics and great detail and will be useful:

http://support.sas.com/techsup/technote/ts648/ts648.pdf

Conclusion: So after reading this paper I hope the readers can use windows scheduler to save time and energy and free their mind from the tension of forgetting to run a job and can spend that extra time with their family.

Will be back with some more SAS magic. Goodbye Till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS DATASTEP MERGE AND SQL JOINS - A comparison

2012-04-03T09:08:00.000-07:00

SAS DATASTEP MERGE AND SQL JOINS

A COMPARISON

Objective: The objective of this post is to get a reader get a good knowledge of datastep merges and SQL joins and provide a comparison between them. Also towards the end I would specifically outline the differences between these two to let you decide which of the two to use depending on your output requirements.

There are other ways as well to combine SAS datasets like multiple SET statements and hashing but I will be discussing them in a separate post.

Datastep merge:

A datastep merge is one of the most heavily used programming constructs in SAS. It helps us to combine two or more SAS datasets and outputs a single combined dataset containing all the variables from both datasets if not specified otherwise. I will be using small datasets to demonstrate the merging process.

Let us first look at the syntax:

Data dset1;

Merge dset2(in=a) dset3(in=b);

By byvar1 byvar2... ;

If a and b;

Run;

The datasets I will be using are as follows:

data a;

input id name $ ;

datalines;

1 a1

2 a2

3 a3

4 a4

;

run;

data b;

input id new_name $ ;

datalines;

1 b1

2 b2

3 b3

4 b4

;

run;

I will be updating these datasets according to requirements.

So first we will see a small example of merging datasets A and B to make a new dataset C.

data c;

merge a(in=a) b(in=b);

by id;

run;

The ouput is as follows:

A B C

This is the simplest form of merge where there is one to one mapping of all the key variables.

Now we will look at proc sql joins and also compare them with corresponding datastep merge and point out the differences if any.

Proc SQL joins:

A Proc SQL join works same as a datastep merge to combine two or more SAS datasets. There are many types of SQL joins which can be used based on your output requirements.

1) Inner Join

An inner join provides only the matching rows from both datasets. Its syntax is:

Proc sql;

create table company as

select a.empid, b.name, b.salary

from employee as a inner join salary as b

where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees having their salary information in the salary dataset. If the info for a employee is found in any one of the datasets then that employee will not be present in the final table.

1a) Inner Join with datastep:

The same inner join can be performed with a datastep merge.

data company;

merge employee(in=a) salary(rename=(emp_id=empid) in=b);

by empid;

if a and b;

run;

We required the rename as datastep cannot perform a merge on id variables with different names.

Actually there is a way to merge by differently named id variables but that method is at from the notion of elegant coding and is just developed as a workaround as you can always rename id variables easily.

OK OK If you require it so much I will discuss the method towards the end.

2) Outer Join (Left)

A Left Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the left dataset or the dataset specified first in the query. Its syntax is:

Proc sql;

create table company as

select a.empid, b.name, b.salary

from employee as a left outer join salary as b

where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees whether or not they have their salary information in the salary dataset. If you want to list all the employees irrespective of their salary being updated in the salary dataset then you will use left outer join.

2a) Left outer Join with datastep:

The same join can be performed with a datastep merge. We just need to change the IF condition.

data company;

merge employee(in=a) salary(rename=(emp_id=empid) in=b);

by empid;

if a;

run;

3) Outer Join (Right)

A Right Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the right dataset or the dataset specified second in the query. Its syntax is:

Proc sql;

create table company as

select a.empid, b.name, b.salary

from employee as a right outer join salary as b

where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees who have their salary information in the salary dataset as well as the employees who are not currently updated in employee dataset but their salary info was updated. The situation looks silly but in this case you will be using a right outer join.

One point to note here is that we are taking empid from the employee dataset so for these non-matching employees of the salary dataset the empid will be missing which is definitely not desirable. So as a precaution we generally use the COALESCE function in right outer joins.

Proc sql;

create table company as

select coalesce(a.empid,b.emp_id), b.name, b.salary

from employee as a left outer join salary as b

where a.empid=b.emp_id;

Quit;

This will solve the problem of missing employee ids.

3a) Right Outer Join with datastep:

The right outer join can be performed with a datastep merge similarly as the left outer.

data company;

merge employee(in=a) salary(rename=(emp_id=empid) in=b);

by empid;

if b;

run;

4) Full Join

A Full join as you have rightly guessed by now outputs the matching rows from both datasets as well as it also outputs the non-matching from both the datasets. Its syntax is:

Proc sql;

create table company as

select coalesce(a.empid,b.emp_id), b.name, b.salary

from employee as a full join salary as b

where a.empid=b.emp_id;

Quit;

Here the final output will be all the employees who have their salary information either in the salary dataset or the employee dataset.

Here also we use COALESCE function or the same reason as in Right outer join.

4a) Full Join with datastep:

The full join can also be performed with a datastep merge. We just need to eliminate the if condition or for better understanding we can keep the condition as IF A OR B;

data company;

merge employee(in=a) salary(rename=(emp_id=empid) in=b);

by empid;

if a or b;

run;

5) Cartesian Product

A simplest join in proc sql where the final out is each row of first dataset combined with each row of the second. Its syntax is:

Proc sql;

create table company as

select a.empid, b.name, b.salary

from employee as a , salary as b

;

Quit;

Here the final output will be product of all rows from the employee dataset with all rows in the salary dataset.

Practically this looks wrong as everyone’s salary will be everyone others’ salary, perfect Socialism J

But surprisingly this Cartesian product is the basis of all joins. Whenever you specify a join a Cartesian product is done and the output rows are restricted by certain conditions. So knowing this is necessary.

6) Cartesian Product through a datastep

This technique is not very intuitive but is asked in a lot of interviews so I am including it here:

data every_combination;

/* Set one of your data sets, usually the larger data set */

set one;

do i=1 to n;

/* For every observation in the first data set, */

/* read in each observation in the second data set */

set two point=i nobs=n;

output;

end;

run;

This technique creates a product of all rows.

So above is the hint for answering the question in merging SAS datasets with different id variables names. Let’s see whether you can find the answer.

7) Many to many joins

These are not a type of join but a property of the data in the tables you are joining. I am including this here as a type of warning. This occurs when both tables have multiple instances of the same key variable. This can lead to unexpected results and the output is different from PROC SQL join to a DATASTEP MERGE in this case as proc sql is Cartesian product based while merge is one to one matching.

You can create a few examples and study the differences this will help you understand the process better.

Below are the some differences between a merge and a join :

MERGE	JOIN
By default MERGE does a one to one join which will have matching rows and all the remaining rown from both datasets	By default a Cartesian Product is produced;It means joining each row from one table to every row of the other table
Default Match merging is the same as full ouetr join in SQL	Match join by default gives the ouptput of an inner-join
Multiple dataset can be used while performing outer join.	Outer join can only be done on two datasets at a time.
Sorting is required.	Sorting is not required.
All variable names in datasets should be identical.	Joining is possible on columns with differing names

Many-to-many merge cannot be done due to one-to-one behavior of MERGE	Relatively easy to do a many-to-many join.
No advantages while working with database tables as databases can do the processing faster.	Very advantageous when working with tables stored in Database Servers because databases are designed for SQL processing.

Conclusion: This paper gives basic information about the SQL merges and joins and is intended to be used as a starter or a reference in this topic.

Will be back with some more SAS magic. Goodbye Till then.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Efficiency Techniques in SAS

2012-03-19T11:26:00.000-07:00

Increase efficiency, earn more money

Efficiency Techniques in SAS

Objective: The first heading is just a fake one to get your attention, now when I have you attention I’ll reveal my real objective. The objective of this post is to discuss some of the techniques which can reduce your code running time and also save you from memory buffer full conditions in case of large data.

The Techniques:

1) DROP KEEP DATASET OPTIONS/STATEMENTS:

Data out (drop=time);

set in;

run;

Data out;

set in (keep=time);

run;

Now you’ll say what new is there in these we use them daily. Yes but it is always worthwhile to give a thought on where you are using them. You are creating a report from Raw data which you will modify to get final data. If you use these options or statements in the last step which is creating final data the these will be of no use in terms of efficiency. So I just want to say that use them smartly or in other way as early as possible so that SAS will be saved from the burden of carrying unuseful variables till the end. Of course you need to plan before you code to identify which variables you’ll need.

Also it is good to keep the options in set statement instead of data statement.

2) USE COLUMN INPUT IF POSSIBLE:

Data out;

Infile in;

input

Name $1-10

Address $20-26;

run;

Column input is the most efficient type of input. Using column style input allows you to create a subsetted version of an external file containing only the variables needed for analysis, as opposed to reading all of the variables in and dropping the superfluous ones.

3) Use subsetting IF:

Always make use of subsetting IF to eliminate unnecessary observations. There is an important point which people generally ignore in using subsetting IF. Always use the subsetting IF(if the condition is based on preexisting variables in a datset) before most of the calculations, as obviously it will save time on doing calculation in observation which will be dropped finally thus saving precious run time when dealing with large datasets.

To prove my point I have taken an example.

I created test1 and test2 datasets with same calculation and output except the in first one the subsetting IF is used after calculations and in the second before calculation.

67 data test1;
68 set test;
69 k=i*5;
70 w=i*6;
71 n=i*7;
72 if i < 10000000;
73 run;

NOTE: There were 100000000 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST1 has 9999999 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 9.43 seconds

74
75 data test2;
76 set test;
77 if i < 10000000;
78 k=i*5;
79 w=i*6;
80 n=i*7;
81 run;

NOTE: There were 100000000 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 9999999 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 8.90 seconds

You can see the time difference yourself with just 10 million observations and just 1 small calculation, so think what wonders can this small change do when applied to huge datasets and lot of calculations.

4) Use Indexes:

Indexes can provide huge efficiency improvements when retrieving a small subset of data from a large dataset. There are multiple ways to create an index, some of them are below:

/*PROC datasets*/

proc datasets lib=work memtype=data;

modify test1;

index create composit=(i k);

run;quit;

/*PROC SQL*/

proc sql;

create index composit on

work.test2(i,k);

quit;

/*DATA Step*/

data work.test3(index=(composit=(i k)));

set work.test3;

run;

Using index is also efficient than sorting a dataset as an indexed dataset in not required to be sorted.

Please note that indexes has certain disadvantages which must be kept in mind before creating indexes :

1) First one is that they take extra SPACE and so if you have a space crunch on your server, avoid indexes.

2) As data in a table is inserted, modified, or deleted, an index must be updated to address those changes. This automatic feature requires additional CPU resources to process any changes to a table.

5) Remote Processing:

SAS/CONNECT Software enables users who use a client install of the SAS System to connect to a remote server. Thereby taking full advantage of the server’s greater storage capacity and processing power. Once a signon script has been executed users may communicate with the server by using RSUBMIT blocks, files may be moved from client to sever and vice versa using either PROC UPLOAD/DOWNLOAD or RLS (Remote Library Services).

For example:

rsubmit;

proc upload in=sashelp out=work;

select class;

data sample;

set class(where=(age > 30));

proc download in=work out=sasuser;

select sample;

run;

endrsubmit;

6) Use IF-THEN-ELSE wisely:

I will explain you with examples what I mean to say :

1) For e.g. you have 6 conditions out of which only one can be true then use and if then else instead of a list of IF conditions as below :

/* Bad use of IF*/

if age le 10 then pocket_money=10;

if age gt 10 and age le 20 then pocket_money=50;

if age gt 20 and age le 30 then pocket_money=50;

if age gt 30 and age le 40 then pocket_money=50;

if age gt 40 and age le 50 then pocket_money=50;

if age gt 50 and age le 60 then pocket_money=50;

/* Good use of IF ELSE */

if age le 10 then pocket_money=10;

else if age gt 10 and age le 20 then pocket_money=50;

else if age gt 20 and age le 30 then pocket_money=50;

else if age gt 30 and age le 40 then pocket_money=50;

else if age gt 40 and age le 50 then pocket_money=50;

else if age gt 50 and age le 60 then pocket_money=50;

Also best will be to list the conditions in IF-THEN-ELSE in decreasing order of probability.

2) While using AND conditions keep the condition first which is more likely to be false and in OR keep the condition first which is more likely to be true as in thse cases the second condition will not be evaluated.

7) Use FORMATS and not IF-THEN-ELSE when assigning values based on criteria:

When you have to assign one value to a variable from a list of values based on a criteria it is more efficient to use proc format than using a series of IF-THEN-ELSE statements.

Example:

/*Less Efficient*/

data out;

set in;

if oldno = 0 then newno="< 3 yrs old";

else if oldno=1 then newno="no school";

else if oldno=2 then newno="nursery school";

else if oldno=8 then newno="11th grade";

else if oldno=9 then newno="12th but nongrad";

run;

/*More efficient:*/

proc format;

value oldnof

0="< 3 yrs old"

1="no school"

2="nursery school"

3="kindergarten"

4="thru 4th grade"

5="thru 8th grade"

6="9th grade"

7="10th grade"

8="11th grade"

9="12th but nongrad";

run;

data out;

set in;

newno=put(oldno,oldnof.);

run;

8) Use the COMPRESS= data set option when creating large SAS datasets:

Using compress option when creating large datasets reduce space usage and Input Output time.

data work.test1(compress=yes);

set work.test;

run;

9) Use concatenation to reduce the number of variables you need to sort by:

Sorting large datasets by multiple variables take up a lot of CPU time. So a tip for improving efficiency is to create a sort variable by concatenating all the variables which are used for sort and use that for sorting. In case you have some numeric variables convert them to character to concatenate.

/*Less Efficient*/

proc sort;

by a1 a2 a3 a4 a5;

run;

/*More efficient*/

data out;

set in;

sortvar=a1||a2||a3||a4||a5;

run;

proc sort;

by sortvar;

run;

Conclusion: These are few of the numerous small techniques you can use while doing your day to day programming and get drastic performance improvements.

So make them a habit because in a world of ever-growing need of computing resources efficiency never hurts..!!

Will be back with some more magic of SAS. Till then Goodbye.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

2012-03-10T22:27:00.000-08:00

DYNAMIC DATA EXCHANGE(DDE) in SAS

A QUICK STARTER

Objective: This post discusses a generally unknown and underutilized utility in SAS which gives programmers a lot of flexibility and power if utilized properly.

Theory: Dynamic data exchange alias DDE is a way for data transfer and exchange between Microsoft windows applications. SAS can utilize this functionality to transfer data to applications like MSexcel or MSword in a flexible and dynamic way.

DDE ADVANTAGES:

I know I know that right now you are saying that why in the world should I learn this new concept when I have PROC IMPORT and PROC EXPORT available, I even can use ODBC or just can import data using SAS EG.

Here is the answer…

When you are talking about proc import and proc export, these procedures are simple to use but provide less functionality to modify you data as DDE uses data step directly to transfer data. Secondly you should have an extra license SAS/ACCESS to use these procedures but DDE is just BASE SAS(windows).

ODBC also has same problem, an extra license.

SAS EG is a complete new SAS Client you have to buy and again the free thing is more flexible than this costly software so why not go for free, after all we love free stuff..!! J

DDE DISADVANTAGES:

Not keeping the debate one sided there are some disadvantages with DDE as well:

1) It works only in windows environment.

2) Application must be running on same computer from which SAS is exchanging data. Though we have a workaround for this, where we will start the application using SAS.

READING DATA USING SAS:

Here I will be discussing 3 ways to read data in SAS from MS Excel:

1) Getting data from clipboard:

This method is very simple, open Excel data, copy data using our favorite Ctrl-C which will send it to clipboard and then we can use SAS LIBNAME statement and DATA STEP to get that data.

First open an Excel file select the data you want to input as SAS dataset and press CTRL-C.

Then in SAS window write the following code:

filename bmi DDE 'CLIPBOARD';

data bmi;

infile bmi missover;

input ID SEX AGE EDUC CIG WEIGHT;

run;

So the dataset bmi is created with the observations copied to the clipboard. Pretty simple isn’t it..!!

Also now as you have the data step working with that data so run your imaginations wild and you can mould the data in any way before outputting it to the dataset.

2) Second technique is similar but it has a slight advantage over the previous technique that it does not require to copy data, but an extra step is also required, you need to find the DDE triplet which is of the form

Application|topic!item

But problem is how you will find the DDE triplet. It can be found out by copying the data from corresponding application then going into the Solutions menu -> Accessories -> DDE Triplet, this will give you the triplet.

Now you will say this has not one step less but 2 steps extra. we had to copy data anyway and then go to solution menu and copy the triplet.

But this can be done once in a reusable code. And the triplet is self intuitive and you don’t need to copy it necessarily, you can type it yourself, I was just telling a way to get it with zero possibility of errors.

Now using the triplet you can write the following code to get your data:

filename bmi DDE 'Excel|F:\SAS\sas datasets\[bmi1.xls]bmi1!R2C1:R10C6';

data bmi;

infile bmi missover;

input ID SEX AGE EDUC CIG WEIGHT;

run;

Same as previous code just CLIPBOARD is replaced with the triplet.You can just change this triplet according to your needs.

NOTE: The application(Excel in this case) should be running when you are running this code.

3) Thirdly, we can just start the application in SAS itself so we are now free from the above requirement of application to be running.

The code goes something like:

OPTIONS NOXSYNC NOXWAIT;

X '"F:\SAS\sasdatasets\bmi1.xls"';

FILENAME bmi DDE 'Excel|F:\SAS\sasdatasets\[bmi1.xls]bmi1!R2C1:R10C6';

DATA sales;

infile bmi missover;

input ID SEX AGE EDUC CIG WEIGHT;

RUN;

The options NOXWAIT and NOXSYNC are mandatory and they tell SAS to not keep on waiting for user input after opening the application and return control.

The X tells SAS that what follows in single quotes is a windows command and just specifying the file path and name tells SAS to open it.

Rest is all same. You specify the DDE triplet and file is read into SAS dataset.

SO I have explained the 3 ways and all three are pretty simple and flexible so use them generously.

Further reading:

Below are a few interesting reads from the topic which will enhance your knowledge further.

http://www2.sas.com/proceedings/sugi31/154-31.pdf

http://analytics.ncsu.edu/sesug/2003/DM08-Li.pdf

Conclusion : After reading this post the users will be having a good workable knowledge on the data exchange techniques outlined above and I will expect you to start utilizing its capabilities.

Will be back with some more magic of SAS. Till then Goodbye.

References:

The little SAS Book 4^th edition.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

SOME USEFUL SAS PROCEDURES

2012-03-04T09:00:00.000-08:00

SOME USEFUL SAS PROCEDURES

Part 2

Objective : This post is a sequel to my first post on procedures where I highlighted some underutilized procedures. The objective of this post is to the readers basic understanding of the capabilities of some underutilized and some common procedures which can be of great help in day to day programming. The objective is not to give exhaustive knowledge about these procedures but to attract readers’ attention to these procs by interesting examples.

Procedures:

The SORT Procedure:

This procedure I guess everyone knows and uses but I think you read on further and most probably you’ll find something interesting. The SORT procedure orders SAS data set observations by the values of one or more character or numeric variables. The SORT procedure either replaces the original data set or creates a new data set.

Syntax :

PROC SORT <collating-sequence-option> <other option(s)>;

BY <DESCENDING> variable-1 <…<DESCENDING> variable-n>;

Example:

proc sort data=employee;

by idnumber;

run;

I included this procedure to give stress on 4 options which are generally asked in lot of interviews so you should know it.

1) DUPOUT= : This option creates a new datasets which will contain the observations deleted by nodupkey or noduprecs, so you can review those later.

2) NODUPKEY : This option deletes observation based on duplicate values of variables provided in by statement.

3) NOUPRECS / NODUP :- This less known option deletes an observation only when values for all the variables match and not just for by variables.

4) TAGSORT :- This option reduces the need for temporary disk space as it only stores the keys while sorting instead of the complete observation. This can be used when you have a huge dataset and not much disk space avalaible, using this can save you from OUT OF DISK SPACE error.

The PRINT Procedure:

The print procedure like proc sort is also one of the most used procedures. But there are lot of options and functionality in print procedure which are unknown to most and get underutilized; I’ll be listing those below. BASE SAS PROCDURES GUIDE has a 72 page chapter dedicated to it so after all this can’t be such naïve procedure as we use it.

The PRINT procedure prints the observations in a SAS data set, using all or some of the variables. You can create a variety of reports ranging from a simple listing to a highly customized report that groups he data and calculates totals and subtotals for numeric variables.

Syntax :

PROC PRINT <option(s)>;

BY <DESCENDING> variable-1 <…<DESCENDING> variable-n><NOTSORTED>;

PAGEBY BY-variable;

SUMBY BY-variable;

ID variable(s) <option>;

SUM variable(s) <option>;

VAR variable(s) <option>;

RUN;

SUM – The sum statement gives totals for numeric values.

SUMBY - Limits the number of sums that appear in the report. The BY-variable identifies a variable that appears in the BY statement in the PROC PRINT step. If the value of the BY variable changes, or if the value of any BY variable that precedes it in the BY statement changes, PROC PRINT prints the sums of all variables listed in the SUM statement.(Yes right! It is not what you thought it is, summing by each by group is done by SUM and BY statements given separately).

PAGEBY : Control page ejects that occur before a page is full.

VAR - Select variables that appear in the report and determine their order.

The FORMAT Procedure:

The FORMAT procedure enables you to:

1) Define your own informats and formats for variables

2) You can print the parts of a catalog that contain informats or

formats.

3) Store descriptions of informats or formats in a SAS data set, and use a SAS data set to create informats or formats.

Syntax :

PROC FORMAT <option(s)>;

EXCLUDE entry(s);

INVALUE <$>name <(informat-option(s))>

value-range-set(s);

PICTURE name <(format-option(s))>

value-range-set-1 <(picture-1-option(s) )>

<…value-range-set-n <(picture-n-option(s))>>;

SELECT entry(s);

VALUE <$>name <(format-option(s))>

value-range-set(s);

run;

Frankly speaking the above syntax doesn’t do anything except scare you, so I’ll be discussing only few uses of proc format which will be very useful in your day to day programming.

1) Defining formats and informat :

I know you all know this but just read it again for revision or for the heck of it J

Here are the 2 basic examples

Proc format;

value gender

1='M'

2='F'

;

run;

For informats you just exchange the value keyword by invalue.

Proc format;

invalue $gender

1='M'

2='F'

;

run;

A good thing to know here are MULTILABEL FORMATS.

MULTILABEL FORMATS :

Mltilabel formats empower you to :

a) Providing overlapping ranges across labels.

b) Secondary labels to same values.

An example for multilabel formats is :

Proc format;

value agefmt (multilabel)

low - 24 = "Under 25"

low - 49 = "Under 50"

low - 74 = "Under 75"

low - 99 = "Under 100"

100 - high = "100 and Above"

;

run;

This format just assigns overlapping ranges to different labels.

The second type of format is

proc format ;

value drugcnt (multilabel)

1 = "Placebo"

2 = "Company 10mg"

2 = "Any Dose"

3 = "Company 20mg"

3 = "Any Dose"

4 = "Comparator"

;

run ;

This format assigns multiple labels to same value.

You might be wondering where these can be used. Their use is very limited and these can only be used in summary procedures like means, summary and tabulate procedures. Use them and see the magic how they solve the problems of counting a subject in multiple categories.

2) Printing formats from unreadable catalogs

The simple way to print a format in catalog is :

proc format library=work fmtlib;

select drugcnt;

run;

To print the complete catalog you can just leave out the select statement.

3) Storing format information to dataset and use a dataset information to create format :

To display or use format information is the Cntlout option.

proc format library=work cntlout=fmts;

select drugcnt;

run;

TO create a format from a dataset we have to have a highly specialized dataset with exact variable names as required by the format statement.

Either you can output the dataset (from now on reffered to as control dataset) using CNTLOUT option and use its structure add values or use its subset as the input control dataset.

Below is the proc contents output for a control dataset :

DATATYPE Char 8 Date/time/datetime?

DECSEP Char 1 Decimal separator

DEFAULT Num 3 Default length

DIG3SEP Char 1 Three-digit separator

EEXCL Char 1 End exclusion

END Char 16 Ending value for format

FILL Char 1 Fill character

FMTNAME Char 32 Format name

FUZZ Num 8 Fuzz value

HLO Char 11 Additional information

LABEL Char 12 Format value label

LANGUAGE Char 8 Language for date strings

LENGTH Num 3 Format length

MAX Num 3 Maximum length

MIN Num 3 Minimum length

MULT Num 8 Multiplier

NOEDIT Num 3 Is picture string noedit?

PREFIX Char 2 Prefix characters

SEXCL Char 1 Start exclusion

TYPE Char 1 Type of format

START Char 32 Starting value for format

The Ones in bold are the minimum required variables in the input control dataset to create a format successfully. Afetr creating the control datset only a small piece of code is left :

proc format cntlin=fmts;

run;

And your format is created. This technique is very useful in creating formats dynamically based on the values received in a dataset.

The PWENCODE Procedure:

A small but useful procedure serving only a single purpose, it encrypts your password using one of its 3 techniques (Generally technique doesn’t matter). SO you can use the encoded password instead of the real password.

Syntax

PROC PWENCODE IN=’password’ <OUT=fileref> <METHOD=encoding-method>;

RUN;

IN= Specify your actual password here

OUT= specifies the file where your encoded password needs to be printed, default is log

You can use this technique to save your encrypted password to a secure file and can read the password in a datastep and use it anywhere without having to specify your actual password anywhere.

The OPTIONS Procedure:

Many of us will consider the heading above as a typing mistake. Everyone has used options but is there an options procedure..??

And if any exists then what extra it will than just specifying options which we can do with just ‘options’ statement!!

Syntax :

PROC OPTIONS <option(s)>;

RUN;

Proc options does 3 jobs :

1) Dsiplays a list of all syste options.

PROC OPTIONS;

RUN;

There are a host of options to control how your listing looks. You can get the complete list and their usage in SAS® 9.2 Procedures Guide. Your log looks something like this :

SAS (r) Proprietary Software Release 9.2 TS1M0

Portable Options:

APPLETLOC=C:\Program Files\SAS\SASGraphJavaApplets\9.2

Location of Java applets

ARMAGENT= ARM Agent to use to collect ARM records

ARMLOC=ARMLOG.LOG Identify location where ARM records are to be written

ARMSUBSYS=(ARM_NONE)

Enable/Disable ARMing of SAS subsystems

NOASYNCHIO Do not enable asynchronous input/output

AUTOSAVELOC= Identifies the location where program editor contents are auto saved

NOAUTOSIGNON SAS/CONNECT remote submit will not automatically attempt to SIGNON

BINDING=DEFAULT Controls the binding edge for duplexed output

BOMFILE Add Byte Order Mark when creating Unicode files

BOTTOMMARGIN=0.000 IN

Bottom margin for printed output

BUFNO=1 Number of buffers for each SAS data set

BUFSIZE=0 Size of buffer for page of SAS data set

BYERR Set the error flag if a null data set is input to the SORT procedure

BYLINE Print the by-line at the beginning of each by-group

BYSORTED Require SAS data set observations to be sorted for BY processing

NOCAPS Do not translate source input to uppercase

NOCARDIMAGE Do not process SAS source and data lines as 80-byte records

CATCACHE=0 Number of SAS catalogs to keep in cache memory

CBUFNO=0 Number of buffers to use for each SAS catalog

2) The second thing it does is to do the above job for a specific option. It also displays some extra information about that option when we use define in the end.

options linesize=80;

proc options option=linesize define;

run;

3) Displays info about the system option groups.

proc options listgroups;

run;

When the option issue has come up I will give a tip which I generally use :

If you require to change the value in a program and then restore its value back after sometime you generally declare that option 2 times once to change and other to reset it back. Though this does the job but is not very smart and you have to go and check the original value. You can use a trick like.

1) Get the option value dynamically. There are two ways to this

data _null_;

call symput(‘lnsz_vlaue’,getoption('linesize'));

run;

%put & lnsz_vlaue;

%macro hello();

%global lnsz_vlaue ;

%let lnsz_vlaue=%sysfunc(getoption(linesize));

%put linesize=&lnsz_vlaue;

%mend;

%hello;

2) Change to your desired value.

options linesize=100;

3) Reset it back to the original value dynamically.

options linesize=&lnsz_vlaue;

Though it does the same job but with this you can be absolutely sure that in future also under different option values this code will work perfectly.

Conclusion : After reading this post the users will be having a good workable knowledge on the procedures outlined above and I will expect them to strat utilizing the capabilities of these procs.

Will be back with some more magic of SAS. Till then Goodbye.

References :

SAS 9.2 procedures guide by SAS Institute.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

Macro Quoting : A quick and easy reference

2012-02-23T08:08:00.001-08:00

Macro Quoting : A quick reference

Objective : This post deals with quoting of macro variables and their use. The final objective of this post is that after reading this you will be able to decide where and why to use a particular quoting function.

Macros are considered to be a tough topic in SAS and within Macros these quoting functions are considered abstruse. But I will say that this topic is not as difficult as it seems and you just need an objective approach to make a correct decision on which function to use.

Theory: The major problem with most programmers is that they just start using hit and trial on quoting functions whenever they feel the need of quoting anything. But only thing you need is an objective strategy.

So first let us discuss why macro quoting is used.

WHY???

I’ll give the answer to this why with an example. Let us take a SAS statement.

%let var=hello;

%put &var;

LOG : hello

Now I want a semicolon after ‘hello’ to be as part of the value of macro variable var. So I write the following code :

%let var=hello;;

%put &var;

LOG : hello

Hey..!! what happened where is my other semi-colon.

What happened here is, SAS considered the first semi-colon as statement end, assigned the value ‘hello’ to var and treated the second semi-colon as empty SAS statement.

So what we need here??? Answer anyone…

Right!! Quoting.

%let var=%str(hello;);

%put &var;

LOG : hello;

Here…now my log danced on my tune.

So macro quoting is used to hide the special meaning of special characters in SAS.

So before saying anything..Quote it..then you’ll never have to say “I did not mean that”.

Next question arises…

WHAT ARE SPECIAL CHARATERS IN SAS???

Special characters are the characters holding a specific meaning in SAS. For e.g. GE is just a set of 2 alphabets but in SAS GE mean "Greater than or equal to".

Some of the special characters are:

blank ; % & ‘ “ ( ) + - = * / < > ^ | , ~ -- # GE LE EQ OR AND GT LT NE IN

Macro quoting should be used to mask these special characters in a SAS program when they are intended to be used as plain text.

WHAT MACRO QUOTING FUNCTIONS ARE AVAILAIBLE???

The available functions are:

Ø %STR

Ø %NRSTR

Ø %BQUOTE

Ø %NRBQUOTE

Ø %QUOTE

Ø %NRQUOTE

Ø %SUPERQ

Ø %UNQUOTE

I’ll discuss their use one by one.

Here one thing to remember is that the %STR and %NRSTR are compile time functions and rest except %SUPERQ are execution time functions.

What is this compile time and execution time???

Macro statements are compiled by macro processor and then executed We need to control when the text string is to be quoted. Some quoting functions remove the meaning during compilation while others remove it during macro execution. When meaning is removed during compilation (%STR and %NRSTR) the resultant text is passed to the processor, but not the function call. The other quoting functions are resolved during the execution of the macro. For these functions the entire call to the function and its text is passed to the macro processor where it is resolved.

In Short,Compile time functions mask the special meaning while compilation of code but not while executing the code. But execution time functions mask the special meaning at execution as well.

The tokens to be masked can be grouped in 3 categories:

Cat1: All special characters except the ones in Cat2 and Cat3

blank ; + - = * / < > ^ | , ~ -- # GE LE EQ OR AND GT LT NE IN ‘’ “”

Cat2: Characters that occur in pair.

‘ “ ( )

Cat3: Macro triggers

& %

Different functions are required to mask each of these categories of characters.

%STR: Masks Cat1 characters at compile time.

Note: A point to note here is that %STR will mask cat2 tokens also if they appear in pairs but not when they appear alone.

%NRSTR: Masks Cat1 and Cat3 characters at compile time.

Note: All function starting with NR do the same job as their counterpart without-NR functions plus one thing extra that is masking macro triggers also.

%QUOTE: This is a execution time function which masks Cat1 characters at execution time.

%NRQUOTE: Masks Cat1 and Cat3 characters at execution time.

%BQUOTE: Also called “blind Quote” for its extra ability to mask Cat2 or unmatched pair tokens. Masks Cat1 and Cat2 characters at execution time.

%NRBQUOTE: Masks Cat1, Cat2 and Cat3 characters at execution time.

Now you see why so many functions, each have a work of its own. Everyonez got unique style..!! J

%UNQUOTE: Works as a cop for the above spoilt youth. This function removes any masking done by each of the above functions

Oh!! I almost forgot %SUPERQ.

%SUPERQ: This execution time quoting function is a separate monster and works in a way different from all the above functions.

This function requires a macro variable name as its argument and is used when you want only first resolution of the macro variable and don’t want anything in its value to resolve further.

It is different form %NRBQUOTE in that it will never try to resolve further the macro expression if either an & or % sign are present in the macro expression after the first resolution.

Conclusion : So if you are reading this it means that you have read the post (Can also mean that you have just scrolled to see the climax… in that case go up and read the complete post) and next time you require quoting anywhere you’ll know which function will do your job best and the outputs will be predictable.

More scuh tips will follow soon. So keep reading guyz.

Will be back with some more SAS magic. Till then Goodbye.

References :

SAS 9.2 macro language reference by SAS Institute.

http://www.ats.ucla.edu/stat/sas/library/nesug99/ad088.pdf

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome

SAS Procedures Part 1

2012-02-10T08:50:00.000-08:00

SOME UNDERUTILIZED SAS PROCEDURES

Part 1

Objective : The objective of this post is to make the readers aware of the capabilities of some unused and some used but underutilized procedures which can be of great help in day to day programming. The objective is not to give exhaustive knowledge about these procedures but to attract readers’ attention to these procs by interesting examples.

Theory: SAS has a long and ever-growing list of procedures, I have covered just a few of them which are generally not used much but are very useful.

Procedures:

1) Proc RANK

This useful procedure is highly underutilized as it does a small job of assigning ranks.

The RANK procedure computes ranks for one or more numeric variables across the observations of a SAS data set and outputs the ranks to a new SAS data set. The syntax of the procedure is :

PROC RANK <option(s)>;

BY <DESCENDING> variable-1

<…<DESCENDING> variable-n>

<NOTSORTED>;

VAR data-set-variables(s);

RANKS new-variables(s);

By – Calculates ranks separately for each by group.

Ranks – Identifes the variable containing ranks. If omitted then original variable is replaced with ranks.

Var – Specifies the variable to be ranked.

There are different types of ranking methods that can be used. For full details about each ranking type read SAS Procedures guide.

This function also calculates statistical ranks.

Note :- One question frequently asked in interviews is, I want to select the third highest salary from employees. General answers are sort and select 3^rd row etc etc.. but proc rank provides an elegent way of doing it.

data employees;

input id salary;

datalines;

1 2000

2 3000

3 1000

4 5000

5 1500

6 1200

;

run;

proc rank data=employees out=salaries(where=(rank=3)) descending;

var salary;

ranks rank;

run;

So now you can select any highest or lowest salary just by changing the number in where condition.

2) Proc COMPARE :

This procedure compares the contents of two sas datasets or selected variables from the same dataset.

Proc compare is generally used for validation of datasets created. For example- I create an inventory dataset for current month which is to be appended to the master inventory dataset. So it is good to do a Proc compare on two datasets excluding the observation so that you can get the differences in formats labels data types etc. before appending.

PROC COMPARE generates the following information about the two data sets that are being compared:

Whether matching variables have different values
Whether one data set has more observations than the other
What variables the two data sets have in common
How many variables are in one data set but not in the other
Whether matching variables have different formats, labels, or types.
A comparison of the values of matching observations.

The NOVALUES option suppresses the part of the output that shows the differences in the values of matching variables

Syntax :

PROC COMPARE base= compare= <option(s)>;

BY <DESCENDING> variable-1 <…<DESCENDING> variable-n> <NOTSORTED>;

ID <DESCENDING> variable-1 <…<DESCENDING> variable-n> <NOTSORTED>;

VAR variable(s);

WITH variable(s);

Run;

By – Produces a separate comparison for each by group.

ID - Identify variables to use to match observations.

Var - Restrict the comparison to values of specific variables.

With – Used to compare variales of different names or two variables from same dataset.

3) Proc CONTENTS:

Syntax :

Proc contents data=dset <out=dset1>;

Run;

The contents procedure is one of the most popular procedures in SAS. Generally this procedure is used to see the contents of a dataset in a listing output to check variable formats, informats, labels etc.

But there is another way to use this procedure, by giving an out= option which stores the comparison results in a output dataset. This dataset contains valuable information about the dataset on which the contents is applied.

This dataset contains very valuable information about the dataset (variables, types, formats, informats, labels etc.), Also it contains few very useful information like sorted by which can be used.

This output dataset can be utilized in automating repetitive processes For e.g. you need to report all datasets in a library and list the variable(s) it is sorted by, then this dataset can be used.

4) Proc COPY :

Syntax :

PROC COPY OUT=libref-1 IN=libref-2 <CLONE|NOCLONE> <CONSTRAINT=YES|NO>

MOVE <ALTER=alter-password>>;

EXCLUDE SAS-file-1 <...SAS-file-n> </ MEMTYPE=mtype>;

SELECT SAS-file-1 <...SAS-file-n> </ <MEMTYPE=mtype>

<ALTER=alter-password>>;

Run;

The COPY procedure copies one or more SAS files from a SAS library. Generally, the COPY procedure functions the same as the COPY statement in the DATASETS procedure. The two differences are as follows:

The IN= argument is required with PROC COPY. In the COPY statement, IN= is optional. If IN= is omitted, the default value is the libref of the procedure input library.
PROC DATASETS cannot work with libraries that allow only sequential data access.

The COPY procedure, along with the XPORT engine and the XML engine, can create and read transport files that can be moved from one host to another. PROC COPY can create transport files only with SAS data sets, not with catalogs or other types of SAS files.

Transporting is a three-step process:

1 Use PROC COPY to copy one or more SAS data sets to a file that is created with either the transport (XPORT) engine or the XML engine. This file is referred to as a transport file and is always a sequential file.

2 After the file is created, you can move it to another operating environment via communications software, such as FTP, or tape. If you use communications software, be sure to move the file in binary format to avoid any type of conversion. If you are moving the file to a mainframe, the file must have certain attributes.

3 After you have successfully moved the file to the receiving host, use PROC COPY to copy the data sets from the transport file to a SAS library.

5) Proc DATASETS :

Proc datasets is a multipurpose procedure designed to accomplish various tasks for managing your sas files. Proc datasets can do the following:

copy SAS files from one SAS library to another
rename SAS files
repair SAS files
delete SAS files
list the SAS files that are contained in a SAS library
list the attributes of a SAS data set, such as:
the date when the data was last modified
whether the data is compressed
whether the data is indexed
he DATASETS Procedure 􀀀 Sample PROC DATASETS Output 289
manipulate passwords on SAS files
append SAS data sets
modify attributes of SAS data sets and variables within the data sets
create and delete indexes on SAS data sets
create and manage audit files for SAS data sets
create and delete integrity constraints on SAS data sets

Syntax:

The syntax of proc datasets is mind-boggling and is no use writing (see syntax in SAS 9.2 procedures guide) as proc datasets support a variety of tasks, I’ll list a few of them which are used frequently.

One good use of proc datasets is to delete all or selected datasets from a library.

proc datasets lib=work kill memtype=data;

run;

quit;

Other important statements are:

APPEND - adds observations from one data set to another. This is most useful when the base file is large and a small file needs to be added.

CHANGE – changes the name of a SAS file in the input data library.

COPY – copies some or all members of one SAS library to another. This is primarily used to move datasets from one system or version to another.

Conclusion : This post aims at creating awareness on some procedures which are utilized to a very small part of their capability or procedures which are very handy for doing a small job but are unpopular.

This is the first post in procedures series, more will follow soon.

Will be back with some more magic of SAS. Till then Goodbye.

Saurabh Singh Chauhan

(er.chauhansaurabh@gmail.com)

Note: Comments and suggestions are always welcome

References :

SAS 9.2 procedures guide by SAS Institute.

READING EXTERNAL DATA FILES IN SAS (Part 2)

2012-01-29T08:36:00.000-08:00

READING EXTERNAL DATA FILES IN SAS

PART 2 : Reading RAW data

Objective : This article aims at discussing various ways of reading raw data in a SAS dataset in an objective manner which is easy to understand for the user and also help the user understand when which method is to be used.

Theory : We SAS programmers are so obsessed to working with datasets that we generally ignore the fact that most of the real world data is not in SAS datasets. The types of data storage include legacy mainframe files, client/server RDBMS, and simple text files on computers. While we want to use SAS to examine, analyze and display this data, SAS datasets are . So we have to convert the data to SAS data sets from its native form.

POINTING SAS TO THE EXTERNAL FILE :There are two ways to point SAS to external files:-

1) FILENAME statements

2) INFILE statements.

INFILE Statement:

INFILE statement is used to tell a DATA step from which file to read when an INPUT statement is executed. If the argument after the keyword ‘INFILE’ is quoted, the SAS Supervisor treats it as an actual external file name. Otherwise, the SAS Supervisor assumes that it is a fileref. An INFILE statement should be used in each data step reading an external file and must be executed before the input statement. You can have more than one INFILE statement, which allows you to read multiple files with a single DATA step.

If the INFILE statement is absent from a DATA step, the INPUT statement will default to INFILE CARDS.

When SAS begins each iteration of a DATA step, it "forgets" from which file(s) it had read. This means that you must be careful to execute an INFILE for each DATA step that must reference an external file.

The syntax of INFILE statement is:

INFILE file-spec <options> <host options>;

file-spec identifies the source of the data.file-spec may have 3 forms:

1) fileref :- fileref assigned to the required external file. The fileref must be assigned before the DATA step by using a FILENAME statement.

2) ‘external-file’ :- specifies the complete path and name of the required external file. This form is equivalent to specifying the external file with a FILENAME statement.

3) CARDS or DATALINES :- indicate that the data immediately follows the CARDS or DATALINES statement at the end of the data step.

options specify SAS options to control reading the file or to provide information about the file.

FILENAME Statement:

This statement creates a file reference that links a physical file with the SAS system. The FILENAME statement should be executed within a SAS session prior to the DATA step in which the fileref is used.

The syntax of the FILENAME statement is:

FILENAME fileref <device-type> ‘external-name’ <host-options>;

fileref is any valid SAS name.

With Device-type you can read and write data from devices rather than files

’external-name’ is the name of the file on the host system. The quotes are required. Some of the values are CATALOG, DDE, EMAIL. DISK is the default device type.

host-options specify operating system specific options.

INPUT STATEMENT BASICS:

The INPUT statement as we have seen in my previous post is used to tell SAS how to read the file. When an INPUT statement is executed, it reads from the file pointed to by the most recently executed INFILE statement, if the data step has more than one INFILE statements.

Variable Lengths: In my last post I discussed about various input methods like list, column, formatted, named. When deciding which method of input to use the length of required variables is an important consideration. SAS determines the length of a variable based on how it is referenced in the program, subject to certain defaults. Numeric variables (including dates) and character variables read by list or named input default to 8 bytes. Character variables read by formatted input or format modified list input are set to the length as specified by the informat. Character variables read by column input are set to the number of columns being read.

We can set the desired lengths of variables using LENGTH and ATTRIB statements which must be given before the INPUT statement.

Input Pointer Controls:

Though I have already discussed these in my previous post but it is required for the completion of topic.

SAS has two pointer controls, the column pointer and the line pointer, We can change these pointers to reread data, change the order in which data fields are read, or handle logical records that are defined by multiple physical records. Use of these pointer controls provide a lot of flexibility to the input statement. Following are the pointer controls in SAS :

1) @expression

2) +expression

@ moves the pointer to the column number specified after it.

+ moves the pointer left or right the number of columns resulting from expression.

3) #expression

4) /

# moves the pointer to a specific line number based on expression.

/ moves the pointer to the beginning of the next line. You can use multiple / to move more than one line.

Line Hold Specifiers: When we have multiple Input statements in the data step or we want to hold the current pointer position within iterations of the data step we use these specifiers. These are placed at the end of the INPUT statement.

They withhold SAS from reading a new record when the next INPUT statement is executed. This technique is used to read more complex files.

@ (trailing at-sign) tells SAS to keep this record current until either an INPUT is executed without a trailing @ or trailing @@, or until this iteration of the DATA step is completed.

@@ (double trailing at-sign) tells SAS to keep this record current through successive iterations of the DATA step.

NOTE : The difference between @ and @@ is a very important interview question and is asked very frequently ( Personal experience J )

INFILE STATEMENT OPTIONS: The INFILE statement has many options, some of which are listed below:

END= This variable becomes 1 when the current record is the last record in the file.

EOF= label defines a label to which the program will automatically branch when an

INPUT statement tries to read past the end of the file.

FIRSTOBS=number specifies the first record to be read. The default value is 1.

OBS=record-number is the number of the last record to read.

Processing the end of records: The options below define what action SAS will take when the program attempts to move the column pointer beyond the end of a record.

FLOWOVER tells SAS to continue reading succeeding records until all variables in the INPUT statement have been read.

MISSOVER tells SAS to set remaining variables in the INPUT statement to missing.

STOPOVER tells SAS to immediately execute a STOP statement, which will stop the DATA step with _ERROR_ equal to 1.

TRUNCOVER tells SAS to salvage whatever it can from short records without going to the next record.

LENGTH=variable defines a variable that is set to the length of the current record. Even though the LENGTH= variable is defined by the INFILE, its value is controlled by the execution of the INPUT statement.

DELIMITER= identifies one or more delimiters to be used with list input. Either a variable name or a quoted constant may be specified. The default delimiter is a blank.

DSD enables you to read delimited files correctly when consecutive delimiters are present due to missing values. It also enables you to read quoted text, a common occurrence when reading files created on desktop systems.

Conclusion : This post as a sequel of my first post explains a host of options used in reading raw data which can be used with those techniques for better control over your reading data. This post is not just for reading but to be practised. SO guyz keep practising.

Will be back with some SAS Magic again. Till then Goodbye..!!

Saurabh Singh Chauhan

Note : Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

READING EXTERNAL DATA FILES IN SAS

2012-01-28T09:53:00.000-08:00

READING EXTERNAL DATA FILES IN SAS

PART 1 : Input methods

Objective : This article aims at discussing various Input methods used read raw data in a SAS dataset in an objective manner which is easy to understand for the user and also help the user understand when and which method is to be used.

Theory : In SAS reading raw data can be accomplished in various ways and which method to use depends purely on the type of data being imported into SAS.

There are four major methods of reading raw data files. These include:

1) List Input

2) Column input

3) Formatted Input

4) Named Input

A) LIST INPUT :

List input sequentially reads your input data lines for data values. Data values do not have to be aligned in columns, but they have to be separated from one another by a delimiter such as a space or other delimiter such as a comma (,). Space or blank is the default delimiter, to specify some othe delimiter use DLM= option in infile statement.

List input requires only that you specify the variable names to be assigned to the data values in your input data line.If you are creating the variable for the first time in the input statement (variable not defined in a length or attrib statement), a dollar sign ($) must follow the name of a character variable.

List input is the most basic form of data reading (of course after cards and datalines..!! ) so it has certain restrictions like :

- Data values MUST be separated by a blank or a delimiter

- A placeholder must specifically show a missing value wherever present(e.g.

period for numeric missing value)

- For reading a character value greater than 8 characters long you have to

specifically define the length in a LENGTH or ATTRIB statement. Etc etc…

Below is the sample code where an external delimited raw data file is read with list input. Assume that an external space delimited file results.dat exists in the given directory.

data results;

infile 'c:\myfiles\results.dat' DLM=’,’;

input rollno name $ age subject1 subject2 subject3 subject4 ;

run;

Simple right ?

This is the most basic way of reading data and as data can be arranged in different ways we have other forms of input.

B) COLUMN INPUT

Column input reads standard data values which are aligned in columns. In a column input a column range is specified after the variable name, if it is a character then a $ follows the variable name which in turn is followed by the column range. Column input solves certain limitation of the above list input method:

- In column input missing values need not be specifically shown with a period or

consecutive delimiters, it just have to be missing..!!

- You can specify column names in input statement in any order you like.

- Character data values can have embedded delimiters.

But it has some limitations as well:

- Data values must be in same column for all data lines (That’s why it is called

column input right!!)

- Leading blanks are removed from the field.

Below is a small example for reading data through column input:

data results;

infile 'c:\myfiles\results.dat';

input roll_no 1-6 name $ 9-20

age 7-8 subject1 21-25

subject2 26-30;

run;

C) FORMATTED INPUT

Unlike the above two cases where we are reading only standard data, formatted input provides us a way to read non-standard data. The @ and + pointers which we use in formatted input provide a lot of flexibility in reading data.

Some advantages of formatted input include:

- Character values can contain embedded blanks.

- Placeholders for missing values are not required.

- We can move through data in a direct manner (@) or a manner relative to

current position from where the data is being read(+) .

- Data values can be specified in any order in the input statement.

@ Pointer tells the pointer to move to the column specified after the @ sign.

+ Pointer tells the pointer to move further columns from the current position which is specified after the + sign.

data patients;

infile 'c:\readdata\results.dat';

input @9 name $12. @1 roll no 6.

@7 age 2. @21 (subject1-subject3) (8. +1) ;

run;

D) MODIFIED LIST INPUT

Modified list input is a combination of list input and formatted input. Like list input, this style is restricted to reading variables in order. The data values do not have to be aligned in columns, but they do have to be separated from one another by a space or other delimiter such as a comma. Additionally, you can include informats that allow you to read more complex data values than you can read with simple list input.

In modified list input we use format modifiers to read complex data. There are three types of format modifiers that are used:

1) AMPERSAND (&) – This modifier specified after the variable name helps in reading data with embedded delimiters. SAS will stop reading the value when 2 consecutive delimiters are encountered.

2) TILDE (~) This character at the top left of your keyboard just below the Esc key tells SAS to read single and double quotation marks and delimiters as part of the data.

3) COLON (:) The colon modifier is used to specify informats for data values in some specific format like dollar8. The informat is specified right after the colon. SAS will read data until the delimiter is encountered or the width specified in the informat is reached.

An example using column input can be like :

data hospital;

infile datalines delimiter=',';

input pat_name : $20.

Med_hist ~ $50.;

datalines;

George Smith,Had a long history of bronchitis

Wiber Stuart,Sensory organs stopped functioning at the age of 14

Rosy Mary,Had acute heart pain and nausea

;;;;

run;

Conclusion : The aim of this report was not to have an exhaustive pile of all concepts to read raw data files but to give a concise and easy understanding of options available for reading raw data files and their merits and demerits over each other.

Reading data is a very interesting concept in SAS and there are many challenges that you can face. So keep practicing with whatever data you get. Other types of data reading will be covered in a different post.

Will be back with some SAS Magic again. Till then Goodbye..!!

Saurabh Singh Chauhan

Note : Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS Character functions(Part 1):A quick reference

2012-01-25T10:06:00.000-08:00

SAS Character functions(Part 1)

A quick reference

Objective : This article aims at creating a one stop solution for all SAS character functions with explanation and usage examples. This is the first of the two posts on SAS Character functions.

Below is the alphabetic list of character functions I have used with a simple examples and usage notes. You can just scroll through to find the function you are searching for.

All the SAS function posts can be found here:
SAS Character function Part 1
SAS Character function Part 2
SAS Date and Time functions Part 1
SAS Date and Time functions Part 2

Function	Usage	Syntax	Function Example	Returns
ANYALNUM	Searches for the first occurrence of an Alphanumeric character in a string. Second argument gives the position to start the search.	ANYALNUM(String <,startpos>)	Anyalnum("%$#sa1",2)	2
ANYALPHA	Searches for the first occurrence of an Alphabet character in a string. Second argument gives the position to start the search.	ANYALPHA(String <,startpos>)	Anyalpha("%$#sa1",1)	4
ANYDIGIT	Searches for the first occurrence of an Alphabet character in a string. Second argument gives the position to start the search.	ANYDIGIT(String <,startpos>)	Anydigit("%$#sa1",1)	6
ANYLOWER	Searches for the first occurrence of an Alphabet character in a string. Second argument gives the position to start the search.	ANYLOWER(String <,startpos>)	Anylower("%$#sa1",1)	4
ANYPUNCT	Searches for the first occurrence of a punctuation (! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { \| } ~) character in a string. Second argument gives the position to start the search.	ANYPUNCT(String <,startpos>)	Anypunct("%$#sa1",1)	1
ANYSPACE	Searches for the first occurrence of an space(whitespace,tab etc.) in a string. Second argument gives the position to start the search.	ANYSPACE(String <,startpos>)	Anyspace("%$ #sa1",1)	3
ANYUPPER	Searches for the first occurrence of a uppercase character in a string. Second argument gives the position to start the search.	ANYUPPER(String <,startpos>)	Anyupper("%$#sa1",1)	0
CAT	Concatenates two or more character strings.	CAT(str1,str2<,str3>...)	CAT("ABC ","B CD"," DEF")	ABC B CD DEF
CATS	Concatenates two or more character strings and removes leading and trailing blanks.	CATS(str1,str2<,str3>...)	CATS("ABC ","B CD"," DEF")	ABCB CDDEF
CATT	Concatenates two or more character strings and removes only trailing blanks blanks.	CATT(str1,str2<,str3>...)	CATT("ABC ","B CD"," DEF")	ABCB CD DEF
CATX	Concatenates two or more character strings. Removes leading and trailing blanks and inserts a charatcter between strings	CATX(sepaerator,str1,str2<,str3>...)	CATX("-","ABC ","B CD"," DEF")	ABC-B CD-DEF
CHAR	Extracts a character froma string from the position given	CHAR(string,position)	CHAR("HELLO",4)	L
CHOOSEC	Select n numbered string from the list of strings. Based on the location passed as an argument.	CHOOSEC(n,str1,str2,str3)	CHOOSEC(2,"vineet","Naveen","Vybhav")	Naveen
CHOOSEN	Select nth number from the list of numbers based on the location passed as an argument.	CHOOSEN(n,str1,str2,str3)	CHOOSEN(2,2,7,6)	7
COALESCEC	Returns first non-missing value from a list of character values	COALESCEC(char1,char2,char3)	COALESCEC(" ","B","c" )	B
COMPBL	Compresses two or more spaces into one.	COMPBL(string)	COMPBL("leo frank murthy")	leo frank murthy
COMPRESS	Compresses the characters specified as second arguments from the string passed as first argument	COMPRESS(char_valu,'compress list')	COMPRESS("anandsingh",'as')	nndingh
COUNT	To count the number of times a given substring appears in a string	COUNT(string,search string)	COUNT("Well fell bell","ell")	3
COUNTC	To count the number of individual characters in a string	COUNTC(string,search char list)	COUNTC("Well fell bell","ell")	9
COUNTW	To count number of words in a character value	COUNTW(string<,delimiters>)	COUNTW("Well,fell bell",",")	2
FIND	To locate a substring within a string. We can provide start position, direction of search and can ignore case	FIND(character-value, find-string <,'modifiers'><,start>)	FIND("hi hello hi", "hello")	4
FINDC	To locate a character within a string. We can provide start position, direction of search and can ignore case	FINDC(character-value, find-characters <,'modifiers'><,start>)	FINDC("hi hello hi", "e")	5
FINDW	To locate a word within a string. We can provide delimiter, start position, direction of search and can ignore case	FINDW(character-value, find-word<,delim> <,'modifiers'><,start>)	FINDW("hi hello hi", "hello")	4

Conclusion : This and the next post is not an exhaustive list of all SAS Character functions available but it covers almost all functions you’ll use in day to day job

And a alphabetical list like this can act as your quick reference or you can even print and stick it on your desk for super quick reference.

I have tried my best to eliminate erros but if any please write it as a comment. Suggestion are always welcome.

Will be back with some more topics. Till then goodbye.

Saurabh Singh Chauhan
(er.chauhansaurabh@gmail.com)

SAS DICTIONARY TABLES AND VIEWS

2012-01-18T10:54:00.000-08:00

SAS DICTIONARY TABLES AND VIEWS

The untapped & underutilized marvels

Objective : SAS maintains valuable information about the tables, views, catalogs present in current session and this information can be utilized for a host of tasks right from maintaining or updating 100s of tables in a go or searching the metadata for any specific requirement .

The collection is so vast that it can’t be imagined upto what extent this info can be utilized to programmer’s advantage. This concept is relatively simple and very powerful but their potential has been underutilized and ignored by sas community.

Here I present very simple and basic steps to use a dictionary table or a view.

Theory : Dictionary tables are tables (technically they can’t be called tables as the information is generated and maintained at runtime, but for simplicity sake they can be used exactly as a table can be) that store all the information about the tables, view, catalogs, options, macros etc in scope of current session.

A POINT TO NOTE HERE IS THAT DICTIONARY TABLES ARE ONLY AVAILAIBLE FOR USE WITHIN PROC SQL AND CANNOT BE USED WITHIN A DATASTEP, FOR THIS REASON THERE IS A CORRESPONDING VIEW WITH EXACTLY SAME INFORMATION IN SASHELP LIBRARY WHICH CAN BE USED IN DATASTEPS,

FOR E.G. DICTIONARY.COLUMNS HAS A CORRESPONDING VIEW SASHELP.VCOLUMN.

These tables and views are a lot in number but you will be using only a few in your work. Below I’ll try to present them in easy steps:

1) First point to note here is that there is a table which stores info about all the dictionary tables which is

called DICTIONARY.DICTIONARIES so you don’t need to remember which tables are present.

Below is a listing of all dictionary tables present taken from DICTIONARIES table :

proc sql;

select distinct memname from dictionary.dictionaries;

quit;

Member Name

--------------------------------

CATALOGS

CHECK_CONSTRAINTS

COLUMNS

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DATAITEMS

DESTINATIONS

DICTIONARIES

ENGINES

EXTFILES

FILTERS

FORMATS

FUNCTIONS

GOPTIONS

INDEXES

INFOMAPS

LIBNAMES

MACROS

MEMBERS

OPTIONS

PROMPTS

PROMPTSXML

REFERENTIAL_CONSTRAINTS

REMEMBER

STYLES

TABLES

TABLE_CONSTRAINTS

TITLES

VIEWS

So you can use all these tables and the information present in it are at your service to be utilized!!

2) Lets take up one dictionary table DICTIONARY.COLUMNS and explore its contents.

proc sql;

describe table dictionary.columns;

quit;

Following is printed in log :

create table DICTIONARY.COLUMNS

(

libname char(8) label='Library Name',

memname char(32) label='Member Name',

memtype char(8) label='Member Type',

name char(32) label='Column Name',

type char(4) label='Column Type',

length num label='Column Length',

npos num label='Column Position',

varnum num label='Column Number in Table',

label char(256) label='Column Label',

format char(49) label='Column Format',

informat char(49) label='Column Informat',

idxusage char(9) label='Column Index Type',

sortedby num label='Order in Key Sequence',

xtype char(12) label='Extended Type',

notnull char(8) label='Not NULL?',

precision num label='Precision',

scale num label='Scale',

transcode char(8) label='Transcoded?'

);

This table lists all columns in every table present in current SAS session. So you can use this dictionary table for uses like counting all numeric columns in a particular table, dynamically creating a list of all tables present in a particular library etc.

3) Some of the most frequently used tables are : TABLES , CATALOGS, FORMATS, VIEWS etc.

4) Here I am mentioning only Dictionary tables and sashelp views. The point being that they are identical things and can be used interchangeably in proc sql code and views can be used ouside proc sql as well. SO we’ll go ahead with dictionary tables and you can assume the same usage for sashelp views. For your reference below is the list of some dictionary tables and their corresponding views and purposes :

Dictionary Table	Sashelp view	Purpose
CATALOGS	VCATALG	Provides information about SAS catalogs.
CHECK_CONSTRAINTS	VCHKCON	Provides check constraints information.
COLUMNS	VCOLUMN	Provides information about column in tables.
CONSTRAINT_COLUMN_USAGE	VCNCOLU	Provides column integrity constraints information.
CONSTRAINT_TABLE_USAGE	VCNTABU	Provides information related to tables with integrity constraints defined.
DICTIONARIES	VDCTNRY	Provides information about all the DICTIONARY tables.
EXTFILES	VEXTFL	Provides information related to external files.
FORMATS	VFORMAT	Provides information related to defined formats and informats.
GOPTIONS	VGOPT	Provides information about currently defined SAS/GRAPH software graphics options.
INDEXES	VINDEX	Provides information related to defined indexes.
LIBNAMES	VLIBNAM	Provides information related to defined SAS data libraries.
MACROS	VMACRO	Provides information related to any defined macros.
MEMBERS	VMEMBER	Provides information related to objects currently defined in SAS data libraries.
OPTIONS	VOPTION	Provides information related to SAS system options.
REFERENTIAL_CONSTRAINTS	VREFCON	Provides information related to tables with referential constraints.
STYLES	VSTYLE	Provides information related to select ODS styles.
TABLES	VTABLE	Provides information related to currently defined tables.
TABLE_CONSTRAINTS	VTABCON	Provides information related to tables containing integrity constraints.
TITLES	VTITLE	Provides information related to currently defined titles and footnotes.
VIEWS	VVIEW	Provides information related to currently defined data views.

Code:

Below I will give you a simple example which will demonstrate the use of these dictionary tables and will help to convince you on its use.

/* Example to delete all datasets in specific library which start wil letter a */

proc sql;

select distinct memname into : list separated by ' '

from dictionary.columns

where libname="WORK" and lowcase(memname) like "a%";

quit;

%put &list;

proc datasets library=work;

delete &list;

quit;

The example above dynamically selects any number of dataset starting with letter ‘a’ from work library and passes it to proc datasets which deletes those.

A similar example can be searching a library for all datasets which contain a given variable :

/* Example to search all datasets in specific library for existence of a specific variable */

proc sql;

select distinct memname into : list separated by ' '

from dictionary.columns

where libname="WORK" and lowcase(compress(name)) eq "id";

quit;

%put &list;

Conclusion : Here I have given very small and easy to understand examples, but you can do wonders with metadata using these tables and view. This facility is very powerful and rest is left to your imagination how to make use of it.

Will be back with some SAS Magic again. Till then Goodbye..!!

Saurabh Singh Chauhan

Note : Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS Autocall macro facility for Windows environment

2012-01-17T10:13:00.000-08:00

Autocall macro facility for Windows environment

My first autocall macro

Objective : This article aims at a simple and easy to use step by step approach to building a precompiled macro library and use it within your program.

Theory : Macros are always compiled before they are used and by default they are stored in SASMACR catalog in work library , SAS provides a powerful feature to pre-compile and store macros permanently. The name looks mind-boggling but it very simple to do it and this can reduce your code writing time as you can store any macro you create and use it in any of your programs without compiling them again.

Before starting a quick over view of the keywords used:

1) MAUTOSOURCE option: This option turns of the ability to use sored autocall macros in SAS. (By

default it is turned on, but just to be sure use this option).

2) SASAUTOS option : This option given the library path where autocall macros are stored. In short this

will specify the location where your compiled macros are stored.

Code:

1) As a first step you need to create a macro which you need to store in permanent library and want to

reuse. For e.g :

%Macro test(var=,dataset=,outdset=);

Data &outdset;

Set &dataset;

If &var le 10 then Name = 'Kid';

Else if 11 le &var le 20 then Name = 'Tennager';

Else if 21 le &var le 40 then Name = 'Adolescent';

Else if &var gt 40 then Name = 'Oldie';

run;

proc print;

run;

%Mend test;

2) Store the above macro in a file with exactly same name in your macro library (you can use any library for e.g c:\myfiles\mymacs).

A point to note here is you should not use any open code outside you macro in that file,

since the macro is compiled only once that code will be executed only the first time.

3) Now to use your stored macro when you are writing a program just enable option MAUTOSOURCE and in SASAUTOS option give your library location and your macro can be used directly :

option sasautos = ('C:\myfiles\mymacs' sasautos);

%test(var=age,dataset=sashelp.class,outdset=new);

Conclusion : So in three easy steps you mastered an arduous task of creating an autocall macro. This facility is very powerful and rest is left to your imagination how to make use of it.

Will be back with some SAS Magic again. Till then Goodbye..!!

Saurabh Singh Chauhan
(er.chauhansaurabh@gmail.com)

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.

SAS MADE EASY

SAS and Machine Learning

Pivot Tables using SAS

An elegant use of call execute-Solution

An elegant use of call execute

How to create a Cartesian product using a datastep ?

SAS Date and Time functions(Part 2)

SAS Character functions(Part 2)

Function

Usage

Syntax

Function Example

Returns

SAS Date and Time functions(Part 1)

Differences in UPADTE and MODIFY statements in SAS

Arrays in SAS

Scheduling SAS jobs using windows scheduler

SAS DATASTEP MERGE AND SQL JOINS - A comparison

Efficiency Techniques in SAS

SOME USEFUL SAS PROCEDURES

Macro Quoting : A quick and easy reference

SAS Procedures Part 1

READING EXTERNAL DATA FILES IN SAS (Part 2)

READING EXTERNAL DATA FILES IN SAS

SAS Character functions(Part 1):A quick reference

SAS DICTIONARY TABLES AND VIEWS

SAS Autocall macro facility for Windows environment