BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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.

 

 

             

3 REPLIES 3
Reeza
Super User

But code dose not working.


What does that mean? 

Tom
Super User Tom
Super User

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
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

I want Grant total monthly and YTD

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 837 views
  • 0 likes
  • 3 in conversation