I created a sample dataset to show what I have and what I want but I don't know how to get there. I am converting SQL code to SAS.
The input:
Row | Member_Order | ID | Member_Name | CHECK_DATE | CLAIM_ID | REVENUE_CODE | DIAGNOSIS_CODES | SERVICE_FROM_DT | SERVICE_THROUGH_DT | PAYOR | Rate | SUM_OF_DAYS | Sum_Of_Claim_Amt | SUM_OF_PAID_AMT |
1 | 1 | 12345 | smith,john | 2/2/2022 | 678910 | 123 | a1 | ######## | ######## | County1 | 838.7097 | 31 | 60000 | 26000 |
2 | 1 | 12345 | smith,john | 3/2/2022 | 111213 | 123 | a1 | 1/1/2022 | ######## | county1 | 838.7097 | 31 | 60000 | 26000 |
3 | 1 | 12345 | smith,john | 3/30/2022 | 141516 | 123 | a1 | 2/1/2022 | ######## | county1 | 1208.333 | 24 | 65000 | 29000 |
4 | 2 | 23456 | doe,jane | 9/21/2022 | 1546 | 123 | b2 | 8/2/2022 | 8/8/2022 | county2 | 2300 | 7 | 16800 | 16100 |
5 | 2 | 23456 | doe,jane | 9/21/2022 | 15465 | 123 | b2 | 8/9/2022 | ######## | county2 | 2300 | 7 | 16800 | 16100 |
6 | 2 | 23456 | doe,jane | 9/28/2022 | 8788 | 123 | b2 | ######## | ######## | county2 | 1788.889 | 9 | 16800 | 16100 |
7 | 2 | 23456 | doe,jane | 10/12/2022 | 212121212 | 123 | b2 | ######## | 9/4/2022 | county2 | 2300 | 7 | 16800 | 16100 |
And the output I want:
ID | Member_name | CHECK_DATE | CLAIM_ID | REVENUE_CODE | DIAGNOSIS_CODES | SERVICE_FROM_DT | SERVICE_THROUGH_DT | SUM_OF_DAYS | SUM_OF_CLAIM_AMT | SUM_OF_PAID_AMT | PAYOR | Rate |
12345 | smith,john | 2/2/2022 | 678910 | 123 | A1 | ######## | ######## | 31 | 60000 | 26000 | County1 | 838.7097 |
12345 | smith,john | 3/2/2022 | 111213 | 123 | A1 | 1/1/2022 | ######## | 31 | 60000 | 26000 | County1 | 838.7097 |
12345 | smith,john | 3/30/2022 | 141516 | 123 | A1 | 2/1/2022 | ######## | 24 | 65000 | 29000 | County1 | 1208.333 |
Total | 381 | 185000 | 81000 | |||||||||
23456 | doe,jane | 9/21/2022 | 1546 | 123 | B2 | 8/2/2022 | 8/8/2022 | 7 | 16800 | 16100 | County2 | 2300 |
23456 | doe,jane | 9/21/2022 | 15465 | 123 | B2 | 8/9/2022 | ######## | 7 | 16800 | 16100 | County2 | 2300 |
23456 | doe,jane | 9/28/2022 | 8788 | 123 | B2 | ######## | ######## | 9 | 16800 | 16100 | County2 | 1788.889 |
23456 | doe,jane | 10/12/2022 | 212121212 | 123 | B2 | ######## | 9/4/2022 | 7 | 16800 | 16100 | County2 | 2300 |
Total | 105 | 67200 | 64400 |
I know I can create this with the totals in Proc Report but I need it as a table to export to Excel. Any ideas on how I can create these subtotals by ID in a dataset?
(All data is made up for the sake of the sample.)
You can use Proc Print and ODS statements to export to Excel. See sample code below using SASHELP.CARS
proc sort data = sashelp.cars out=cars;
by make;
run;
ods excel file = "c:\temp\cars.xlsx" options(sheet_interval='none');
proc print data=cars;
id make;
by make;
sum msrp invoice;
run;
ods excel close;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.