topic Re: PROC REPORT OR PROC TRANSPOSE in ODS and Base Reporting

PROC REPORT OR PROC TRANSPOSE

JHE — Mon, 30 Oct 2017 15:44:12 GMT

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.

Re: PROC REPORT OR PROC TRANSPOSE

Reeza — Mon, 30 Oct 2017 15:48:23 GMT

But code dose not working.

What does that mean?

Re: PROC REPORT OR PROC TRANSPOSE

Tom — Mon, 30 Oct 2017 16:19:33 GMT

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

Re: PROC REPORT OR PROC TRANSPOSE

JHE — Mon, 30 Oct 2017 16:25:52 GMT

I want Grant total monthly and YTD