The SAS Output Delivery System and reporting techniques

PROC REPORT OR PROC TRANSPOSE

Reply
Frequent Contributor
Frequent Contributor
Posts: 117

PROC REPORT OR PROC TRANSPOSE

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.

 

 

             

Super User
Posts: 22,820

Re: PROC REPORT OR PROC TRANSPOSE

But code dose not working.


What does that mean? 

Super User
Super User
Posts: 7,844

Re: PROC REPORT OR PROC TRANSPOSE

[ Edited ]

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
Frequent Contributor
Frequent Contributor
Posts: 117

Re: PROC REPORT OR PROC TRANSPOSE

I want Grant total monthly and YTD

Ask a Question
Discussion stats
  • 3 replies
  • 194 views
  • 0 likes
  • 3 in conversation