Base SAS - Logical Sub-setting part 1 (Covering Where)

How to subset a data logically in SAS

In day to day work, we need to fetch only selective data for analysis purposes e.g. for one analysis, we need data only for female customer, for another data for male customers between age 14 - 40 might be required. Such type of conditional sub-settings, we have named as logical sub-setting.

How to do it  ... explore here with examples.


In SAS, we use where and If statements...let's learn both one by one by example.

Let's first check two very simple examples :



In the code code above, it would give us data for both John and Johny as both name contain Joh ...

Now sometimes there are more than one conditions, we need to apply in conjunction. In such case, we need to use logical operators in such case:



Let's now take this up with an example.

Let's use the following data, copy and paste the below code to make the data (Insurance).


Data Insurance;
infile datalines ;
input  Name $  Gender $ Year  Claim_amount Policy_Type : $20. Sum_assured Monthly_Income 
;
Datalines;
VINOD M 2009 6069 ENDOWMENT 300000 21000
DEEPA F 2010 9911 TERM 100000 7000
DINESH M 2009 7147 TERM 400000 28000
MONAL F 2011 9418 TERM 200000 14000
NEETU F 2011 9635 ENDOWMENT 200000 14000
VINOD M 2010 6105 WHOLELIFE 100000 7000
TANGO M 2010 5914 TERM 400000 28000
RAJAT M 2011 7599 ENDOWMENT 300000 21000
NEETU F 2011 1466 TERM 200000 14000
MONAL F 2012 5265 TERM 200000 14000
BALJEET M 2010 8197 ENDOWMENT 500000 35000
SANTOSH M 2011 1078 ENDOWMENT 400000 28000
RAJAT M 2012 1006 WHOLELIFE 400000 28000
DINESH M 2010 3739 WHOLELIFE 500000 35000
DEEPA F 2011 3818 TERM 200000 14000
;
Run;

Now let's solve the following 9 queries:

Create the following data sets by using the where clause :

1. List out the insured people who has term policy
2. Insured getting salary in the range 10000-25000
3. People who claimed in year 2009 and 2010
4. two different dataset for male and female.
5. Insured whose name starting with ‘S ‘ or ‘V’
6. Insured whose name ending with ‘H’ OR ‘T’
7. Insured whose name consist at least one ‘N’
8. Insured whose name does not consist ‘N’
9. Insured who has term policy and getting salary in range of 15000-30000 claimed in the year 2009.

Answers:

1. List out the insured people who has term policy

data Insurance_1;
set insurance;
where policy_type = "TERM";
Run;

2. Insured getting salary in the range 10000-25000

data Insurance_2;
set insurance;
WHERE monthly_income between 10000 and 25000;
Run;
proc print;
run;

or

data Insurance_2;
set insurance;
WHERE monthly_income > 10000 and monthly_income < 25000;
Run;
proc print;
run;

3. People who claimed in year 2009 and 2010

data Insurance_3;
set insurance;
WHERE year in (2009,2010);
Run;
proc print;
run;

4. two different dataset for male and female.

data female ;
set insurance ;
where gender='F';
run;
data male ;
set insurance ;
where gender=‘M';
run;

5. Insured whose name starting with ‘S ‘ or ‘V’

Data Insurance_5;
set insurance;
WHERE name like 'S%' or name like 'V%' ;
Run;

% is called wild character, S% translates to a character with first letter as "S" and then anything ...
S%T means that a character starting with S and ending with T. It can be of any length. There is one more wild card character _ , which means anything of 1 length only.

6. Insured whose name ending with ‘H’ OR ‘T’

Data Insurance_6;
set insurance;
WHERE name like '%H' or name like '%T' ;
Run;

7. Insured whose name consist at least one ‘N’
data Insurance_7;
set insurance;
WHERE name contains 'N' ;
Run;

8. Insured whose name does not consist ‘N’

data Insurance_8;
set insurance;
WHERE name not contains 'N' ;
Run;

9. Insured who has term policy and getting salary in range of 15000-30000 claimed in the year 2009.

data high_income_9;
set insurance;
WHERE policy_type = 'TERM'
and (Monthly_income ge 15000 and monthly_income le 30000)
and year = 2009 ;
Run;
proc print;
run;

Post solving  the 9 questions illustrated above, I don't think you would find any problem working with Where statement.

Click on the undermentioned link to check the next part covering IF and contrast between Where and IF.



Enjoy reading our other articles and stay tuned with ...

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.