Frequency of a value under more than one variable in SAS


#1

Hi,

I have a policy level data set where each policy can have multiple riders and each rider has unique code. Now I want to count how many policies have a specific rider code (R001), this code can be under columns Rider1, Rider2 and Rider3. I am not able to figure out how to do this with this using SAS.

Any help would be appreciated!!

Example Dataset

    Pol_ID         Rider1                Rider2               Rider3
     P001           R002                  R001                 R002
     P002           R001                  R001                 R003
     P003           R002                  R003                 R001
     P004           R001                  R001                 R001

Regards,
Rahul


#2

@Rahul

You can perform this by reshaping the data using Proc Transpose. Look at the below steps to perform this:-

Step-1: Reshape the data set

Example data set:

Data Test;
Input Pol_ID$ Rider1$ Rider2$ Rider3$;
datalines;
     P001           R002                  R001                 R002
     P002           R001                  R001                 R003
     P003           R002                  R003                 R001
     P004           R001                  R001                 R001
;
Run;

Transpose the data set

Proc Transpose data=Test Out=Check;
  by Pol_ID;
  var Rider1-Rider3;
run;

Step-2: Count rider (R001) with where clause using proc sql

Proc Sql;
Create Table Rider_Count as
Select Pol_Id, Count(Pol_ID) as Rider_Cnt from Check where Col1='R001' group by Pol_ID;
Quit;

OR, you can also have policy and rider wise count.

Proc Sql;
Create Table Rider_Count as
Select Pol_Id, Col1, Count(Pol_ID) as Rider_Cnt from Check group by Pol_ID, Col1;
Quit;

Hope this helps!

Regards,
Sunil