Help using Base SAS procedures

Number of Claims by quarter of year

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Number of Claims by quarter of year

[ Edited ]

Hello Everyone,

 

 

I have a large dataset of health insurance claims of the following structure:

 

data claims;

input client year quarter specialty;

datalines;

1 2012 1 22

1 2012 1 22

1 2012 1 30

2 2013 2 10

2 2013 2 10

;

run;

 

I would like to: 

1. Create variables for each specialty (1 to 90) named spec1, spec 2, etc. 

2. Place the sum of claims per quarter of year and client from that specialty in the approrpriate variable cell. 

 

My desired output would look something like this:

clientyearquarterSpecialtySpec22Spec10spec30
12012122200
12012122200
12012130001
22013210020
22013210020

 

 Thanks for your help.

 


Accepted Solutions
Solution
‎06-09-2017 03:23 PM
Super User
Posts: 17,912

Re: Number of Claims by quarter of year

Use your favourite summary proc (proc means/univariate/summary/sql) to summarize to quarter and then use PROC TRANSPOSE to reformat the data.

 

 

View solution in original post


All Replies
Super User
Posts: 10,538

Re: Number of Claims by quarter of year

Is this for a report? If not what exactly will you do with the resulting data? For many purposes changing data to a "wide" format as you requiest complicates a large number of further operations.

Contributor
Posts: 47

Re: Number of Claims by quarter of year

[ Edited ]

Hi 

 

 

 

clientyearquarterSpecialtySpec22Spec10spec30
12012122201
22013210020

 

 

Solution
‎06-09-2017 03:23 PM
Super User
Posts: 17,912

Re: Number of Claims by quarter of year

Use your favourite summary proc (proc means/univariate/summary/sql) to summarize to quarter and then use PROC TRANSPOSE to reformat the data.

 

 

Contributor
Posts: 47

Re: Number of Claims by quarter of year

I didn't think of that. Thanks for the tip.

Contributor
Posts: 47

Re: Number of Claims by quarter of year

Hello 

 

I am trying your method with proc transpose. I need to see the number of claims per CLIENT by specialty and quarter of year. I have about 4 million claims from probably a few million different clients, multiplied by the number of periods.

 

I have this so far, but I somehow doubt that this is going to work for SAS: 

 

PROC tabulate DATA=work.claims out=claims_out;

CLASS client year quarter specialty;

VAR specialty;

tables client*year*quarter*specialty, N;

title "Claims by Specialty and quarter";

run;

 

Any suggestions?

Super User
Posts: 17,912

Re: Number of Claims by quarter of year

I thought you were trying to count the number of clients? If so, why is that in your table statement?

 

If it's just counts use proc freq. 

 

Proc freq data=have noprint;
Table year*quarter*specialty / out = summary;
Run;

Proc transpose data=summary out=want prefix=spec_;
By year quarter;
Var count;
Id specialty;
Run;
Super User
Posts: 17,912

Re: Number of Claims by quarter of year

If you do want client level summary include it in the TABLE statement and BY statement for the TRANSPOSE. 

Contributor
Posts: 47

Summarize clients by quarter of year and specialty

Hello Everyone,

 

I have a large dataset of health insurance claims of the following structure:

 

data claims;

input client year quarter specialty;

datalines;

1 2012 1 22

1 2012 1 22

1 2012 1 30

2 2013 2 10

2 2013 2 10

;

run;

 

I would like to summarize the number of claims per CLIENT by specialty and quarter of years. In other words, I would like to collapse my data by quarters.

 

My desired output would look something like this:

 

clientyearquarterSpecialtySpec22Spec10spec30
12012122201
22013210020

 

Reeza on this forum suggested using proc tabulate (or something similar) and then proc transpose. But my problem is that I have 4.5 million claims from milions of clients. And this seems to be too much for SAS to write out. 

 

This is what I've tried so far:

 

PROC tabulate DATA=work.claims out=claims_out;

CLASS client year quarter;

VAR specialty;

tables client*year*quarter*specialty, N;

title "Claims by Specialty and quarter";

run;

 

Any suggestions?

Frequent Contributor
Posts: 110

Re: Summarize clients by quarter of year and specialty

 

The following code yields the desired outcome: 

 

proc sql;
  create table work.claims_out as
    select *, 
       	sum(Case when specialty=22 then 1 else 0 end) as Spec22,
    	sum(Case when specialty=10 then 1 else 0 end) as Spec10,     
   		sum(Case when specialty=30 then 1 else 0 end) as Spec30
    from work.claims
    group by client, year, quarter;
quit;

proc sort data=work.claims_out nodupkey;
	by client year quarter;
quit;

 

Contributor
Posts: 47

Re: Summarize clients by quarter of year and specialty

Thanks Yegen. In other words, this is not possible with Proc Tabulate...

 

One problem I have with this code, is that I actually have 90 specialties. Is there a way I don't have to manually generate 90 Spec variables?

 

Thanks again.

Super User
Posts: 6,963

Re: Summarize clients by quarter of year and specialty

Sort by client, year, quarter and speciality.

Do a proc summary with the same by statement.

Determine the needed columns with a proc sort nodupkey by speciality.

Write the data step that does the transpose (or create it dynamically with call execute from the proc sort result).

 

How many "specialities" are there, anyway?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 47

Re: Summarize clients by quarter of year and specialty

Super User
Posts: 5,093

Re: Summarize clients by quarter of year and specialty

You should be able to get closer to your desired output with a few changes:

 

PROC tabulate DATA=work.claims out=claims_out;

CLASS client year quarter specialty;

tables client*year*quarter, specialty;

title "Claims by Specialty and quarter";

run;

 

This is untested code, so may need some tweaking.

 

This still won't get you SPECIALTY column you indicate, however.  But your logic for obtaining it is not clear.  Should it be the first specialty within that Client / Year  / Quarter  The most frequently occurring specialty?  Should it be removed entirely?

Contributor
Posts: 47

Re: Summarize clients by quarter of year and specialty

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 243 views
  • 1 like
  • 7 in conversation