topic Re: Summarize clients by quarter of year and specialty in SAS Procedures

Number of Claims by quarter of year

GKati — Fri, 09 Jun 2017 14:26:07 GMT

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 30

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:

client	year	quarter	Specialty	Spec22	Spec10	spec30
1	2012	1	22	2	0	0
1	2012	1	22	2	0	0
1	2012	1	30	0	0	1
2	2013	2	10	0	2	0
2	2013	2	10	0	2	0

Thanks for your help.

Re: Number of Claims by quarter of year

ballardw — Fri, 09 Jun 2017 14:29:42 GMT

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.

Re: Number of Claims by quarter of year

GKati — Fri, 09 Jun 2017 14:39:30 GMT

Hi ballardw,

Am I changing it to wide? Not exactly, because that wouldn't give me the sum of claims per quarter as the value. Correct?

It is difficult to explain exactly. Eventually, I would like to collapse the dataset by quarter of year. Similar to this:

client	year	quarter	Specialty	Spec22	Spec10	spec30
1	2012	1	22	2	0	1
2	2013	2	10	0	2	0

And merge this into my client dataset. At the moment, my problem is that i have several (sometimes 400) claims per client from different specialties and hospitals, and I can't merge the two dataset appropriately.

Thanks for your comment.

Re: Number of Claims by quarter of year

Reeza — Fri, 09 Jun 2017 14:48:38 GMT

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

Re: Number of Claims by quarter of year

GKati — Fri, 09 Jun 2017 19:24:31 GMT

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

Re: Number of Claims by quarter of year

GKati — Sat, 10 Jun 2017 05:37:34 GMT

Hello Reeza,

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?

Summarize clients by quarter of year and specialty

GKati — Sat, 10 Jun 2017 06:26:42 GMT

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 30

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:

client	year	quarter	Specialty	Spec22	Spec10	spec30
1	2012	1	22	2	0	1
2	2013	2	10	0	2	0

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?

Re: Summarize clients by quarter of year and specialty

Yegen — Sat, 10 Jun 2017 09:36:33 GMT

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;

Re: Summarize clients by quarter of year and specialty

GKati — Sat, 10 Jun 2017 10:45:56 GMT

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.

Re: Summarize clients by quarter of year and specialty

Kurt_Bremser — Sat, 10 Jun 2017 10:56:59 GMT

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?

Re: Summarize clients by quarter of year and specialty

GKati — Sat, 10 Jun 2017 11:04:10 GMT

KurtBremser: There are about 90 specialties. Thanks for your comment. I will try it.

Re: Summarize clients by quarter of year and specialty

Astounding — Sat, 10 Jun 2017 11:04:43 GMT

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?

Re: Summarize clients by quarter of year and specialty

Ksharp — Sat, 10 Jun 2017 11:28:50 GMT

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;
proc freq data=claims noprint;
table client*year*quarter*specialty/out=have nopercent nocum ;
run;
proc sql noprint;
 select distinct cats('spec',specialty) into : list separated by ' '
  from have;
quit;
data want;
 set have;
 by client year quarter;
 array x{*} &list;
 retain &list;

if first.quarter then do;
 do i=1 to dim(x);
  x{i}=0;
 end;
end;

 do i=1 to dim(x);
  if cats('spec',specialty)=vname(x{i}) then do;
    x{i}=count;leave;
  end;
 end;

if last.quarter;
drop i specialty count percent;
run;

Re: Summarize clients by quarter of year and specialty

GKati — Sat, 10 Jun 2017 14:53:24 GMT

Astounding: I am trying to create a claims dataset with basic information on each claim, that I can merge in with the clients dataset. I have many (sometimes 400 claims) per client that I need information about in one dataset. Hence, the new dataset structure.

Re: Number of Claims by quarter of year

Reeza — Sat, 10 Jun 2017 15:21:40 GMT

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;

Re: Summarize clients by quarter of year and specialty

GKati — Sat, 10 Jun 2017 15:24:34 GMT

Thanks for your comment Ksharp. I get the following error message.

Data want;

43 set have;

44 by client year quarter;

45 array x{*} &list;

NOTE: Line generated by the macro variable "LIST".

45 spec. spec0 spec1 spec10 spec11 spec13 spec16 spec17 spec18 spec2 spec20 spec21 spec22 spec23 spec24 spec26 spec27

_____

201

45 ! spec28 spec29 spec3 spec30 spec35 spec37 spec4 spec5 spec6 spec60 spec61 spec62 spec7 spec8 spec89 spec90

ERROR 22-322: Syntax error, expecting one of the following: a name, a numeric constant, $, (, ;, _ALL_, _CHARACTER_, _CHAR_,

_NUMERIC_, _TEMPORARY_.

ERROR 201-322: The option is not recognized and will be ignored.

46 retain &list;

NOTE: Line generated by the macro variable "LIST".

46 spec. spec0 spec1 spec10 spec11 spec13 spec16 spec17 spec18 spec2 spec20 spec21 spec22 spec23 spec24 spec26 spec27

_____

201

46 ! spec28 spec29 spec3 spec30 spec35 spec37 spec4 spec5 spec6 spec60 spec61 spec62 spec7 spec8 spec89 spec90

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 201-322: The option is not recognized and will be ignored.

48 if first.quarter then do;

49 do i=1 to dim(x);

50 x{i}=0;

51 end;

52 end;

53 do i=1 to dim(x);

54 if cats('spec',specialty)=vname(x{i}) then do;

55 x{i}=count;leave;

56 end;

57 end;

Re: Number of Claims by quarter of year

Reeza — Sat, 10 Jun 2017 15:40:48 GMT

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

Re: Summarize clients by quarter of year and specialty

Reeza — Sat, 10 Jun 2017 15:46:02 GMT

Question/Solution is same as here:

https://communities.sas.com/t5/SAS-Procedures/Number-of-Claims-by-quarter-of-year/m-p/365886#M64790

Re: Summarize clients by quarter of year and specialty

Ksharp — Sun, 11 Jun 2017 04:17:26 GMT

You have missing value in it, so you got 'spec.' .

what variable name you want to assign to missing value ?

Re: Summarize clients by quarter of year and specialty

Kurt_Bremser — Mon, 12 Jun 2017 06:44:23 GMT

I merged the threads with an identical question. @GKati: please keep your questions in one thread in the future. We're not in the business of reinventing wheels.