Creating a Proc Report as a SAS dataset

SASuser8564 — Wed, 14 Jun 2023 18:26:05 GMT

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.)

Re: Creating a Proc Report as a SAS dataset

JOL — Fri, 30 Jun 2023 14:58:26 GMT

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;

Re: Creating a Proc Report as a SAS dataset

Cynthia_sas — Sat, 01 Jul 2023 14:02:11 GMT

Hi: If you can get the look of the table you want, you could either send the PROC REPORT output directly to ODS EXCEL OR, you can use the OUT= option with PROC REPORT to create an OUTPUT dataset and then you could send that dataset to Excel.
Cynthia

topic Re: Creating a Proc Report as a SAS dataset in SAS Programming

Creating a Proc Report as a SAS dataset

Re: Creating a Proc Report as a SAS dataset

Re: Creating a Proc Report as a SAS dataset