BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yash82
Calcite | Level 5

Hi Guys, I am stuck in preparing proc tabulate output as I am kind of newbie on this. I am providing my input data and template here. Please share your views

Input

Period   group  Class   code year  Number  Gross at start  Gross between  Gross at end 

201112  Medical  Opened  at  start - open  2010  100  100.00  100.00  100.00

201112  Medical  Open at start - closed with payment  2009  122  133.00  144.00  155.00

201112  Medical  Open at start - closed without payment  2010  140  160.00  180.00  200.00

201112  Medical  Incurred during the year - open  2011  160  190.00  220.00  250.00

201112  Medical  Incurred during the year -  closed with payment  2011  182  223.00  264.00  305.00

201112  Medical  Incurred during the year -  closed without payment  2011  204  256.00  308.00  360.00

201112  Medical  Reopened during the year - open  2010  226  289.00  352.00  415.00

201112  Medical  Reopened during the year -  closed with payment  2010  246  319.00  392.00  465.00

201112  Medical  Reopened during the year -  closed without payment  2010  266  349.00  432.00  515.00

Template is attached

A1-A10 is  Values of Gross at start  where code=Opened  at  start - open

B1-B10 is Values of Gross between  where code=Opened  at  start - open

C1-B10 is Values of Gross at end  where code=Opened  at  start - open

D1-D10 is  Values of Gross at start  where code=Open at start - closed with payment

E1-E10 is Values of Gross between  where code=Open at start - closed with payment

F1-F10 is Values of Gross at end  where code=Open at start - closed with payment

G1-G10 is  Values of Gross at start  where code=Open at start - closed without payment

H1-H10 is Values of Gross between  where code=Open at start - closed without payment

I1-I10 is Values of Gross at end  where code=Open at start - closed without payment

I have managed to produce the output but its not producing correctly as desired. I think I am making mistakes somewhere in the class and Var. Any help would be really appreciable


template.png
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Your report is a little complicated.

I am not sure whether the code below is you need.

data temp;
infile datalines expandtabs truncover;
input Year     claimtype & $100.     beginamount     middleamount endamount     claimno     (claimsubtype  break1     break2     break3     break4) (& $100.);
datalines;
2005     Incurred during the year - closed with payment     182         318      428        537         Claims incurred during the year   .     Closed Claims at the end of the period:       .       settled 
2006     Incurred during the year - closed with payment     212      331        441      550         Claims incurred during the year      .     Closed Claims at the end of the period:       .      settled
2006     Incurred during the year - closed without payment     184       319        429      538        Claims incurred during the year       .     Closed Claims at the end of the period:       settled without payment        .
2007     Incurred during the year - closed without payment     213       332         442        551      Claims incurred during the year       .     Closed Claims at the end of the period:       settled without payment        .
2007     Incurred during the year - open         177        320         430      539         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .        settled
2008     Incurred during the year - open        214      333         443         552         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2008     Open at the beginning of the year - closed with payment         192         321         431         540         RBNS claims. Open Claims at the beginning of the year     .     Closed Claims at the end of the period:          .       settled
2009     Open at the beginning of the year - closed with payment        215         334      444         553         RBNS claims. Open Claims at the beginning of the year     .     Closed Claims at the end of the period:          .       settled
2009     Open at the beginning of the year - closed without payment         267      322         432         541         RBNS claims. Open Claims at the beginning of the year     .       Closed Claims at the end of the period:        settled without payment       .
2010     Open at the beginning of the year - closed without payment         216         335         445      554         RBNS claims. Open Claims at the beginning of the year     .       Closed Claims at the end of the period:         settled without payment       .
2010     Open at the beginning of the year - open     266       323     433         542         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2011     Open at the beginning of the year - open     217       336         446         555      RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2008     Reopened during the year - closed with payment     218         337      447         556        Reopen Claims during the year     .     Closed Claims at the end of the period:       settled without payment           .
2011     Reopened during the year - closed with payment     260         324      434         543         Reopen Claims during the year     .     Closed Claims at the end of the period:       settled without payment           .
2009     Reopened during the year - open        262         326         436         545        Claims incurred during the year     Open Claims at the end of the period       .       .       settled
2010     Reopened during the year - open         220        339         449        558        Claims incurred during the year     Open Claims at the end of the period       .       .       settled
;
run;
proc tabulate data=temp missing ;
class year claimsubtype;
class break1;
class break2 ;
class break3 ;
class break4;
var claimno beginamount middleamount endamount;
table year,claimsubtype*(break1*(claimno beginamount middleamount endamount) break2*(break3*(claimno beginamount middleamount) break4*(claimno beginamount)  )  );
keylabel sum=' ';
run;


Ksharp

View solution in original post

10 REPLIES 10
GreggB
Pyrite | Level 9

it may  help if you paste your code in so we can look at what you have.

Ksharp
Super User

Not understand what you mean totally.

data temp;
input Period   group $  Class_code & $40. year  Number  Gross_at_start  Gross_between  Gross_at_end; 
datalines;
201112  Medical   Opened at start - open   2010  100  100.00  100.00  100.00
201112  Medical   Open at start - closed with payment   2009  122  133.00  144.00  155.00
201112  Medical   Open at start - closed without payment   2010  140  160.00  180.00  200.00
;
run; 
proc report data=temp nowd;
column Period  Class_code,(Gross_at_start  Gross_between  Gross_at_end );
define period/group;
define class_code/across;
rbreak after/summarize dol;
run;



Ksharp

yash82
Calcite | Level 5

Hi Ksharp,

Is it possible to produce the desired output using Proc Tabulate ???

proc tabulate data=temp nowd;

var Period  Class_code,(Gross_at_start  Gross_between  Gross_at_end );

tables

period,( Class_code *Gross_at_start * Gross_between * Gross_at_end)

run;

I did this but its not producing what we require. Can you help me ??

Ksharp
Super User

Sure. Of course.

data temp;
input Period   group $  Class_code & $40. year  Number  Gross_at_start  Gross_between  Gross_at_end; 
datalines;
201112  Medical   Opened at start - open   2010  100  100.00  100.00  100.00
201112  Medical   Open at start - closed with payment   2009  122  133.00  144.00  155.00
201112  Medical   Open at start - closed without payment   2010  140  160.00  180.00  200.00
;
run; 
proc tabulate data=temp ;
class  Period  Class_code;
var  Gross_at_start  Gross_between  Gross_at_end ;
table Period ,Class_code*(Gross_at_start  Gross_between  Gross_at_end );
run;



Ksharp

yash82
Calcite | Level 5

Hi Ksharp,

I applied your logicc but somehow my variable name is getting displayed in output... like instead of class code , I want  open and closed .. and also my title1 is not displayed...

also how can I further break my class into settled and settled without payment... Let me put another set of sample data...

data temp;

input Period   group $  Class_code & $40. year  Number  Gross_at_start  Gross_between  Gross_at_end;

datalines;

201112  Medical   Opened at start - open   2010  100  100.00  100.00  100.00

201112  Medical   Open at start - closed with payment   2009  122  133.00  144.00  155.00

201112  Medical   Open at start - closed without payment   2010  140  160.00  180.00  200.00

201111 Medical Settled with payment no pending 2004 130 200 500 800

201012 Medical Settled without payment 2008 100 100 100 500

;

run;

Hope I am making sense and thanks a lot for helping me out... Really appreciated...

Ksharp
Super User

I think your post is very obscure. Where are OPEN CLOSE come from?

Where are settled and settled without payment. come from?

I think you need use the data you posted to instead of A1 B1...

That will be more clear your problem.

Ksharp

yash82
Calcite | Level 5

Hi Ksharp, Look at this sample data

Yearclaimtypebegin amountmiddle amountend amountclaim noclaim subtypebreak1break2break3break4
2005Incurred during the year - closed with payment182318428537Claims incurred during the yearClosed Claims at the end of the period:settled
2006Incurred during the year - closed with payment212331441550Claims incurred during the yearClosed Claims at the end of the period:settled
2006Incurred during the year - closed without payment184319429538Claims incurred during the yearClosed Claims at the end of the period:settled without payment
2007Incurred during the year - closed without payment213332442551Claims incurred during the yearClosed Claims at the end of the period:settled without payment
2007Incurred during the year - open177320430539RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Incurred during the year - open214333443552RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Open at the beginning of the year - closed with payment192321431540RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled
2009Open at the beginning of the year - closed with payment215334444553RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled
2009Open at the beginning of the year - closed without payment267322432541RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled without payment
2010Open at the beginning of the year - closed without payment216335445554RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled without payment
2010Open at the beginning of the year - open266323433542RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2011Open at the beginning of the year - open217336446555RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Reopened during the year - closed with payment218337447556Reopen Claims during the yearClosed Claims at the end of the period:settled without payment
2011Reopened during the year - closed with payment260324434543Reopen Claims during the yearClosed Claims at the end of the period:settled without payment
2009Reopened during the year - open262326436545Claims incurred during the yearOpen Claims at the end of the periodsettled
2010Reopened during the year - open220339449558Claims incurred during the yearOpen Claims at the end of the periodsettled

Here, I have need to break the data based on condition of tt1 i.e. if tt1 is 'RBNS claims. Open Claims at the beginning of the year' then I should print only three columns and for remaining conditions I should print all 4 columns . Also in case of closed claims I must sub categorize it into settled with payment and settled without payment.  Something like this

GrossRBNS claims. Open Claims at the beginning of the yearClaims incurred during the yearReopen Claims during the year
Open Claims at the end of the periodClosed Claims at the end of the period:Open Claims at the end of the periodClosed Claims at the end of the period:Open Claims at the end of the periodClosed Claims at the end of the period:
settledsettled without any paymentsettledsettled without any payment
Year
(AY or UWY)
Number of claimsGross RBNS at the beginning of the yearGross payments made during the current yearGross RBNS at the end of the periodNumber of claims ended with paymentsGross RBNS at the beginning of the yearGross payments made during the current yearNumber of claims ended without any paymentsGross RBNS at the beginning of the year referred to claim settled without any paymentNumber of claimsGross payments made during the current yearGross RBNS at the end of the periodNumber of claims ended with paymentsGross payments made during the current yearNumber of claims ended without any paymentsNumber of claimsGross payments made during the current yearGross RBNS at the end of the periodNumber of claims ended with paymentsGross payments made during the current year
(y1) N - 10 + previous yearA1B1C1D1E1F1G1H1I1J1K1L1M1N1O1P1Q1R1S1T1
(y2) N - 9A2B2C2D2E2F2G2H2I2J2K2L2M2N2O2P2Q2R2S2T2
(y3) N - 8A3B3C3D3E3F3G3H3I3J3K3L3M3N3O3P3Q3R3S3T3
(y4) N - 7A4B4C4D4E4F4G4H4I4J4K4L4M4N4O4P4Q4R4S4T4
(y5) N - 6A5B5C5D5E5F5G5H5I5J5K5L5M5N5O5P5Q5R5S5T5
(y6) N - 5A6B6C6D6E6F6G6H6I6J6K6L6M6N6O6P6Q6R6S6T6
(y7) N - 4A7B7C7D7E7F7G7H7I7J7K7L7M7N7O7P7Q7R7S7T7
(y8) N - 3A8B8C8D8E8F8G8H8I8J8K8L8M8N8O8P8Q8R8S8T8
(y9) N - 2A9B9C9D9E9F9G9H9I9J9K9L9M9N9O9P9Q9R9S9T9
(y10) N - 1A10B10C10D10E10F10G10H10I10J10K10L10M10N10O10P10Q10R10S10T10
Total previous year(a1)=Sum(A1:A10)(b1)=Sum(B1:B10)(c1)=Sum(C1:C10)(d1)=Sum(D1:D10)(e1)=Sum(E1:E10)(f1)=Sum(F1:F10)(g1)=Sum(G1:G10)(h1)=Sum(h1:h10)(i1)=Sum(I1:I10)(j1)=Sum(J1:J10)(k1)=Sum(K1:K10)(l1)=Sum(L1:L10)(m1)=Sum(M1:M10)(n1)=Sum(N1:N10)(o1)=Sum(O1:O10)(p1)=Sum(P1:P10)(q1)=Sum(Q1:Q10)(r1)=Sum(R1:R10)(s1)=Sum(S1:S10)(t1)=Sum(T1:T10)

I am getting all analysis variables so far by using your logic but I am stuck in producing conditional output . Hope I am making sense.

yash82
Calcite | Level 5

Some of portion got cut in printing. Take a look at the sample data again

Yearclaimtypebegin amountmiddle amountend amountclaim noclaim subtypebreak1break2break3break4
2005Incurred during the year - closed with payment182318428537Claims incurred during the yearClosed Claims at the end of the period:settled
2006Incurred during the year - closed with payment212331441550Claims incurred during the yearClosed Claims at the end of the period:settled
2006Incurred during the year - closed without payment184319429538Claims incurred during the yearClosed Claims at the end of the period:settled without payment
2007Incurred during the year - closed without payment213332442551Claims incurred during the yearClosed Claims at the end of the period:settled without payment
2007Incurred during the year - open177320430539RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Incurred during the year - open214333443552RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Open at the beginning of the year - closed with payment192321431540RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled
2009Open at the beginning of the year - closed with payment215334444553RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled
2009Open at the beginning of the year - closed without payment267322432541RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled without payment
2010Open at the beginning of the year - closed without payment216335445554RBNS claims. Open Claims at the beginning of the yearClosed Claims at the end of the period:settled without payment
2010Open at the beginning of the year - open266323433542RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2011Open at the beginning of the year - open217336446555RBNS claims. Open Claims at the beginning of the yearOpen Claims at the end of the periodsettled
2008Reopened during the year - closed with payment218337447556Reopen Claims during the yearClosed Claims at the end of the period:settled without payment
2011Reopened during the year - closed with payment260324434543Reopen Claims during the yearClosed Claims at the end of the period:settled without payment
2009Reopened during the year - open262326436545Claims incurred during the yearOpen Claims at the end of the periodsettled
2010Reopened during the year - open220339449558Claims incurred during the yearOpen Claims at the end of the periodsettled

Ksharp
Super User

Your report is a little complicated.

I am not sure whether the code below is you need.

data temp;
infile datalines expandtabs truncover;
input Year     claimtype & $100.     beginamount     middleamount endamount     claimno     (claimsubtype  break1     break2     break3     break4) (& $100.);
datalines;
2005     Incurred during the year - closed with payment     182         318      428        537         Claims incurred during the year   .     Closed Claims at the end of the period:       .       settled 
2006     Incurred during the year - closed with payment     212      331        441      550         Claims incurred during the year      .     Closed Claims at the end of the period:       .      settled
2006     Incurred during the year - closed without payment     184       319        429      538        Claims incurred during the year       .     Closed Claims at the end of the period:       settled without payment        .
2007     Incurred during the year - closed without payment     213       332         442        551      Claims incurred during the year       .     Closed Claims at the end of the period:       settled without payment        .
2007     Incurred during the year - open         177        320         430      539         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .        settled
2008     Incurred during the year - open        214      333         443         552         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2008     Open at the beginning of the year - closed with payment         192         321         431         540         RBNS claims. Open Claims at the beginning of the year     .     Closed Claims at the end of the period:          .       settled
2009     Open at the beginning of the year - closed with payment        215         334      444         553         RBNS claims. Open Claims at the beginning of the year     .     Closed Claims at the end of the period:          .       settled
2009     Open at the beginning of the year - closed without payment         267      322         432         541         RBNS claims. Open Claims at the beginning of the year     .       Closed Claims at the end of the period:        settled without payment       .
2010     Open at the beginning of the year - closed without payment         216         335         445      554         RBNS claims. Open Claims at the beginning of the year     .       Closed Claims at the end of the period:         settled without payment       .
2010     Open at the beginning of the year - open     266       323     433         542         RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2011     Open at the beginning of the year - open     217       336         446         555      RBNS claims. Open Claims at the beginning of the year     Open Claims at the end of the period       .       .       settled
2008     Reopened during the year - closed with payment     218         337      447         556        Reopen Claims during the year     .     Closed Claims at the end of the period:       settled without payment           .
2011     Reopened during the year - closed with payment     260         324      434         543         Reopen Claims during the year     .     Closed Claims at the end of the period:       settled without payment           .
2009     Reopened during the year - open        262         326         436         545        Claims incurred during the year     Open Claims at the end of the period       .       .       settled
2010     Reopened during the year - open         220        339         449        558        Claims incurred during the year     Open Claims at the end of the period       .       .       settled
;
run;
proc tabulate data=temp missing ;
class year claimsubtype;
class break1;
class break2 ;
class break3 ;
class break4;
var claimno beginamount middleamount endamount;
table year,claimsubtype*(break1*(claimno beginamount middleamount endamount) break2*(break3*(claimno beginamount middleamount) break4*(claimno beginamount)  )  );
keylabel sum=' ';
run;


Ksharp

yash82
Calcite | Level 5

Thanks a lot Ksharp.... This is what exactly produced the desired output......

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1155 views
  • 4 likes
  • 3 in conversation