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
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
it may help if you paste your code in so we can look at what you have.
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
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 ??
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
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...
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
Hi Ksharp, Look at this sample data
Year | claimtype | begin amount | middle amount | end amount | claim no | claim subtype | break1 | break2 | break3 | break4 |
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 |
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
Gross | RBNS claims. Open Claims at the beginning of the year | Claims incurred during the year | Reopen Claims during the year | |||||||||||||||||
Open Claims at the end of the period | Closed Claims at the end of the period: | Open Claims at the end of the period | Closed Claims at the end of the period: | Open Claims at the end of the period | Closed Claims at the end of the period: | |||||||||||||||
settled | settled without any payment | settled | settled without any payment | |||||||||||||||||
Year (AY or UWY) | Number of claims | Gross RBNS at the beginning of the year | Gross payments made during the current year | Gross RBNS at the end of the period | Number of claims ended with payments | Gross RBNS at the beginning of the year | Gross payments made during the current year | Number of claims ended without any payments | Gross RBNS at the beginning of the year referred to claim settled without any payment | Number of claims | Gross payments made during the current year | Gross RBNS at the end of the period | Number of claims ended with payments | Gross payments made during the current year | Number of claims ended without any payments | Number of claims | Gross payments made during the current year | Gross RBNS at the end of the period | Number of claims ended with payments | Gross payments made during the current year |
(y1) N - 10 + previous year | A1 | B1 | C1 | D1 | E1 | F1 | G1 | H1 | I1 | J1 | K1 | L1 | M1 | N1 | O1 | P1 | Q1 | R1 | S1 | T1 |
(y2) N - 9 | A2 | B2 | C2 | D2 | E2 | F2 | G2 | H2 | I2 | J2 | K2 | L2 | M2 | N2 | O2 | P2 | Q2 | R2 | S2 | T2 |
(y3) N - 8 | A3 | B3 | C3 | D3 | E3 | F3 | G3 | H3 | I3 | J3 | K3 | L3 | M3 | N3 | O3 | P3 | Q3 | R3 | S3 | T3 |
(y4) N - 7 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | H4 | I4 | J4 | K4 | L4 | M4 | N4 | O4 | P4 | Q4 | R4 | S4 | T4 |
(y5) N - 6 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | H5 | I5 | J5 | K5 | L5 | M5 | N5 | O5 | P5 | Q5 | R5 | S5 | T5 |
(y6) N - 5 | A6 | B6 | C6 | D6 | E6 | F6 | G6 | H6 | I6 | J6 | K6 | L6 | M6 | N6 | O6 | P6 | Q6 | R6 | S6 | T6 |
(y7) N - 4 | A7 | B7 | C7 | D7 | E7 | F7 | G7 | H7 | I7 | J7 | K7 | L7 | M7 | N7 | O7 | P7 | Q7 | R7 | S7 | T7 |
(y8) N - 3 | A8 | B8 | C8 | D8 | E8 | F8 | G8 | H8 | I8 | J8 | K8 | L8 | M8 | N8 | O8 | P8 | Q8 | R8 | S8 | T8 |
(y9) N - 2 | A9 | B9 | C9 | D9 | E9 | F9 | G9 | H9 | I9 | J9 | K9 | L9 | M9 | N9 | O9 | P9 | Q9 | R9 | S9 | T9 |
(y10) N - 1 | A10 | B10 | C10 | D10 | E10 | F10 | G10 | H10 | I10 | J10 | K10 | L10 | M10 | N10 | O10 | P10 | Q10 | R10 | S10 | T10 |
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.
Some of portion got cut in printing. Take a look at the sample data again
Year | claimtype | begin amount | middle amount | end amount | claim no | claim subtype | break1 | break2 | break3 | break4 |
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 |
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
Thanks a lot Ksharp.... This is what exactly produced the desired output......
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.
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.