topic Re: Using Proc Report how to represent data which has single information in SAS Programming

Using Proc Report how to represent data which has single information

hammerman — Tue, 07 Jun 2016 00:45:16 GMT

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	Total Number of Debts	Total 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

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

Ksharp — Tue, 07 Jun 2016 03:47:51 GMT

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;

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

hammerman — Tue, 07 Jun 2016 03:59:26 GMT

Wow PROC TABULATE is very handy. Cheers mate

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