DATA Step, Macro, Functions and more

Using Proc Report how to represent data which has single information

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Using Proc Report how to represent data which has single information

Have a data set which has the following:

 

DATA HAVE :

STATE No_of_Customers No_Of_debts Source_TYPE Amount
KS 647 1275 Other 19574.34
KS 1798 2157 Agency 1669878.93
KS 23 33 Repayments 53472.76
KS 473 566 Tax paid 365126.33
KS 8110 10008 Withholdings 5032969.95
KS 11 12 TP Withholdings 5443.66
KS 147 189 Compensation payments 3774599.18
KS 4845 5907 Customer Payment 5151133.52
KS 6486 8382 Family Reconciliation 5447675.48
KS 1654 1909 Refund -1606948.69
KL 16908 30476 Other 2527167.48
KL 40104 47810 Agency 37739517.97
KL 532 699 Repayments 1667726.54
KL 10923 12919 Tax paid 8896202.28
KL 231500 288113 Withholdings 149990001.9
KL 285 296 TP Withholdings 178254.52
KL 4057 4800 Compensation payments 52065377.03
KL 107966 131555 Customer Payment 113182647.1
KL 132702 175140 Family Reconciliation 118751100.8
KL 36757 42032 Refund -32737393.32
IL 607 1254 Other 32551.3
IL 1202 1478 Agency 1114419.37
IL 66 79 Repayments 85137.52
IL 898 1101 Tax paid 765992.98
IL 13718 18213 Withholdings 8277894.63
IL 10 10 TP Withholdings 9379.8
IL 37 39 Compensation payments 292409.98
IL 2498 3184 Customer Payment 2327173.21
IL 4948 6697 Family Reconciliation 4702521.98

 

Want to create an excel output which looks like the following:

 

 

    Withholdings TP Withholdings Agency Tax paid Repayments Compensation payments Customer Payment Family Reconciliation Other Refund Total Number of Debts Total Value Repaid
State Number of Customers  Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid Number of Debts  Value Repaid
KS 24194 10008 5032969.95 12 5443.66 2157 1669878.93 566 365126.33 33 53472.76 189 3774599.18 5907 5151133.52 8382 5447675.48 1275 19574.34 1909 -1606948.69 30438 19912925.46
KL 581734 288113 149990001.9 296 178254.52 47810 37739517.97 12919 8896202.28 699 1667726.54 4800 52065377.03 131555 113182647.1 175140 118751100.8 30476 2527176.48 42032 -32737393.32 733840 452260611.3
IL 25592 18213 8277894.63 10 9379.8 1478 1114419.37 1101 765992.98 79 85137.52 39 292409.98 3184 2327173.21 6697 4702521.98 1254 32551.3 1845 -1540629.1 33900 16066851.67
Total 631520 316334 163300866.5 318 193077.98 51445 40523816.27 14586 10027321.59 811 1806336.82 5028 56132386.19 140646 120660953.8 190219 128901298.3 33005 2579302.12 45786 -35884971.11 798178 488240388.4

 

 

 

 


Accepted Solutions
Solution
‎06-06-2016 11:58 PM
Super User
Posts: 10,023

Re: Using Proc Report how to represent data which has single information

Posted in reply to hammerman

It is good for PROC TABULATE.

 

data have;
infile cards dlm='09'x truncover;
input STATE	$ No_of_Customers	No_Of_debts	Source_TYPE	: $20. Amount;
cards;
KS	647	1275	Other	19574.34
KS	1798	2157	Agency	1669878.93
KS	23	33	Repayments	53472.76
KS	473	566	Tax paid	365126.33
KS	8110	10008	Withholdings	5032969.95
KS	11	12	TP Withholdings	5443.66
KS	147	189	Compensation payments	3774599.18
KS	4845	5907	Customer Payment	5151133.52
KS	6486	8382	Family Reconciliation	5447675.48
KS	1654	1909	Refund	-1606948.69
KL	16908	30476	Other	2527167.48
KL	40104	47810	Agency	37739517.97
KL	532	699	Repayments	1667726.54
KL	10923	12919	Tax paid	8896202.28
KL	231500	288113	Withholdings	149990001.9
KL	285	296	TP Withholdings	178254.52
KL	4057	4800	Compensation payments	52065377.03
KL	107966	131555	Customer Payment	113182647.1
KL	132702	175140	Family Reconciliation	118751100.8
KL	36757	42032	Refund	-32737393.32
IL	607	1254	Other	32551.3
IL	1202	1478	Agency	1114419.37
IL	66	79	Repayments	85137.52
IL	898	1101	Tax paid	765992.98
IL	13718	18213	Withholdings	8277894.63
IL	10	10	TP Withholdings	9379.8
IL	37	39	Compensation payments	292409.98
IL	2498	3184	Customer Payment	2327173.21
IL	4948	6697	Family Reconciliation	4702521.98
;
run;
proc tabulate data=have ;
class STATE Source_TYPE;
var No_of_Customers	No_Of_debts	Amount;
table STATE all,No_of_Customers*sum 
        Source_TYPE*(No_Of_debts Amount='Value Repaid') 
        No_Of_debts='Total Number of Debts'*sum 
        Amount='Total Value Repaid'*sum;
keylabel sum=' ';
run;

View solution in original post


All Replies
Solution
‎06-06-2016 11:58 PM
Super User
Posts: 10,023

Re: Using Proc Report how to represent data which has single information

Posted in reply to hammerman

It is good for PROC TABULATE.

 

data have;
infile cards dlm='09'x truncover;
input STATE	$ No_of_Customers	No_Of_debts	Source_TYPE	: $20. Amount;
cards;
KS	647	1275	Other	19574.34
KS	1798	2157	Agency	1669878.93
KS	23	33	Repayments	53472.76
KS	473	566	Tax paid	365126.33
KS	8110	10008	Withholdings	5032969.95
KS	11	12	TP Withholdings	5443.66
KS	147	189	Compensation payments	3774599.18
KS	4845	5907	Customer Payment	5151133.52
KS	6486	8382	Family Reconciliation	5447675.48
KS	1654	1909	Refund	-1606948.69
KL	16908	30476	Other	2527167.48
KL	40104	47810	Agency	37739517.97
KL	532	699	Repayments	1667726.54
KL	10923	12919	Tax paid	8896202.28
KL	231500	288113	Withholdings	149990001.9
KL	285	296	TP Withholdings	178254.52
KL	4057	4800	Compensation payments	52065377.03
KL	107966	131555	Customer Payment	113182647.1
KL	132702	175140	Family Reconciliation	118751100.8
KL	36757	42032	Refund	-32737393.32
IL	607	1254	Other	32551.3
IL	1202	1478	Agency	1114419.37
IL	66	79	Repayments	85137.52
IL	898	1101	Tax paid	765992.98
IL	13718	18213	Withholdings	8277894.63
IL	10	10	TP Withholdings	9379.8
IL	37	39	Compensation payments	292409.98
IL	2498	3184	Customer Payment	2327173.21
IL	4948	6697	Family Reconciliation	4702521.98
;
run;
proc tabulate data=have ;
class STATE Source_TYPE;
var No_of_Customers	No_Of_debts	Amount;
table STATE all,No_of_Customers*sum 
        Source_TYPE*(No_Of_debts Amount='Value Repaid') 
        No_Of_debts='Total Number of Debts'*sum 
        Amount='Total Value Repaid'*sum;
keylabel sum=' ';
run;
Occasional Contributor
Posts: 13

Re: Using Proc Report how to represent data which has single information

Wow PROC TABULATE is very handy. Cheers mate

All I need is to put in an ODS....

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 177 views
  • 1 like
  • 2 in conversation