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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.