I have data set as attached excel file as HDM_PAYMENT_PLAN, try to have report as
LOB Service _Type Month HDM_PROVIDER HDM_PAYMENT
with grand total monthly, and YTD.
My SAS code:
PROC REPORT DATA=HDM_PAYMENT_PLAN;
COLUMNS LOB SERVICE_TYPE MONTH HDM_PROVIDER HDM_PAYMNET TOTAL;
DEFINE LOB/DISPLAY 'LOB';
DEFINE MONTH/DISPLAY 'MONTH';
DEFINE SERVICE_TYPE/DISPLAY 'SERVICE_TYPE';
DEFINE HDM_PROVIDER/DISPLAY 'HDM_PROVIDER';
DEFINE TOTAL/COMPUTED FORMAT= DOLLAR12.2 'HDM_PAYMNET';
RBREAK AFTER MONTH/SUMMARIZE UL;
COMPUTE BEFORE MONTH;
TOTAL=SUM(HDM_PAYMNET);
ENDCOMP;
RUN;
But code dose not working.
Also, if I want using Proc Traspose
PROC SORT DATA=HDM_PAYMENT_PLAN OUT=HDM_PAYMENT_PLANSORT;
BY MONTH LOB SERVICE_TYPE HDM_PROVIDER
RUN;
PROC TRANSPOSE DATA = HDM_PAYMENT_PLANSORT (NAME=DAOP) LET;
ID MONTH;
BY SERVICE_TYPE HDM_PROVIDER;
RUN;
Dose not working either, do not know what is wrong , what supporse chane can make it working.
But code dose not working.
What does that mean?
What do you mean by not working? Are you getting errors in the log?
Why did you post data as XLSX file instead of simple text?
The column name in the XLSX has PAYMENT misspelled. Is that the issue?
data have ;
length LOB 8 SERVICE_TYPE $3 MONTH $7 HDM_PROVIDER $8 HDM_PAYMENT 8;
input LOB SERVICE_TYPE MONTH HDM_PROVIDER HDM_PAYMENT ;
informat HDM_PAYMENT comma. ;
cards;
2900 HDM 2016-08 AA $0.00
2900 HDM 2016-08 AAD $22,600.06
2900 HDM 2016-10 ACD $868.00
2900 HDM 2016-11 ABD $0.00
2900 HDM 2016-12 DD $1,634.94
2900 HDM 2017-01 ACR $2,940.00
2900 HDM 2017-01 BCD $485.00
2900 HDM 2017-03 EEF $1,587.60
2900 HDM 2017-04 LEG $158.20
2900 HDM 2017-04 AAC $158.20
2900 HDM 2017-06 ABD $421.19
2900 HDM 2017-07 FGR $125.00
2900 HDM 2017-08 CDF $99.90
2900 HDM 2017-08 HTS $2,976.50
;
Do you just want to add a cumulative sum column?
data want ;
set have ;
by lob service_type month ;
if first.month then total=hdm_payment;
else total+hdm_payment;
run;
SERVICE_ HDM_ HDM_ Obs LOB TYPE MONTH PROVIDER PAYMENT total 1 2900 HDM 2016-08 AA 0.00 0.00 2 2900 HDM 2016-08 AAD 22600.06 22600.06 3 2900 HDM 2016-10 ACD 868.00 868.00 4 2900 HDM 2016-11 ABD 0.00 0.00 5 2900 HDM 2016-12 DD 1634.94 1634.94 6 2900 HDM 2017-01 ACR 2940.00 2940.00 7 2900 HDM 2017-01 BCD 485.00 3425.00 8 2900 HDM 2017-03 EEF 1587.60 1587.60 9 2900 HDM 2017-04 LEG 158.20 158.20 10 2900 HDM 2017-04 AAC 158.20 316.40 11 2900 HDM 2017-06 ABD 421.19 421.19 12 2900 HDM 2017-07 FGR 125.00 125.00 13 2900 HDM 2017-08 CDF 99.90 99.90 14 2900 HDM 2017-08 HTS 2976.50 3076.40
I want Grant total monthly and YTD
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.