topic Re: Aggregrate Date and Total in SAS Programming

Aggregrate Date and Total

nupedoc79 — Wed, 11 Nov 2015 19:21:11 GMT

Given the below code I would like to produce a SAS dataset containing observations with single date and total. As shown by the 2 lines highlighted in RED below. However, the logic is producing the dataset with repeating dates and summarized totals. Any suggestions as to what should be done to produce the desired results.

%let begdate = 201501;
%let enddate = 201506;

proc sql;
   create table test as
   select t1.Paid_Date              as Paid_Dte format=YYMMN6.,
          sum(t1.Adjusted_Paid_Amt) as Adjusted_Paid_Amount

FROM rpt_prod.rv_claims_medical t1,
rpt_prod.rv_group_product t2

where t1.group_product_sk = t2.group_product_sk and
t1.Paid_Date_ccyymm between ("&begdate") and ("&enddate")

group by t1.paid_date
;

Paid_Dte	Adjusted_Paid_Amount
201501	16565.31
201501	2003159.95
201502	2152220.5
201502	1169554.64
201501	2019725.26
201502	3321775.14

quit;

Re: Aggregrate Date and Total

PaigeMiller — Wed, 11 Nov 2015 19:26:01 GMT

I'm not sure why your PROC SQL doesn't work, but unless we see the input data, its hard to diagnose.

Re: Aggregrate Date and Total

Tom — Wed, 11 Nov 2015 19:39:17 GMT

Because PROC SQL is grouping by the actual value and not the formatted value.

Instead of applying a format, transform the data values.

proc sql;
  create table test as 
    select intnx('month',t1.Paid_Date,0,'b') as Paid_Dte format=YYMMN6.
         , sum(t1.Adjusted_Paid_Amt) as Adjusted_Paid_Amount     
     from rpt_prod.rv_claims_medical t1
        , rpt_prod.rv_group_product  t2
     where t1.group_product_sk = t2.group_product_sk 
       and t1.Paid_Date_ccyymm between "&begdate" and "&enddate"
     group by Paid_Dte 
  ;

Re: Aggregrate Date and Total

PGStats — Wed, 11 Nov 2015 20:41:27 GMT

It"s much safer (and effective) to keep dates as SAS dates when manipulating data. Keep formatted values for reporting. I would suggest :

%let begdate = 01JAN2015;
%let enddate = 30JUN2015;
proc sql;
   create table test as 
   select 
		intnx("MONTH", t1.Paid_date, 0) as Paid_month format=YYMMN6.,
		sum(t1.Adjusted_Paid_Amt) as Adjusted_Paid_Amount     
	from
		rpt_prod.rv_claims_medical as t1, 
        rpt_prod.rv_group_product  as t2
	where 
		t1.group_product_sk = t2.group_product_sk and 
        t1.Paid_date between "&begdate."d and "&enddate."d 
           
 group by calculated Paid_month;
 quit;

(untested)

Also, make sure you are not summing up the same Adjusted_Paid_Amt multiple times if many entries from table rv_group_product can correspond to the same entry in table rv_claims_medical.

Re: Aggregrate Date and Total

nupedoc79 — Mon, 14 Dec 2015 11:44:50 GMT

I apologize for the delay but thanks all the below code worked. Once I got it working I was able to use the intnx statement in Query Builder's advanced expression box which was my goal.

%let begdate = 01JAN2015;
%let enddate = 30JUN2015;

proc sql;
create table test as
    select intnx('month',t1.Paid_Date,0) as Paid_Dte format=YYMMN6.,
           sum(t1.Adjusted_Paid_Amt) as Adjusted_Paid_Amount format=dollars12.2

     from rpt_prod.rv_claims_medical t1,
          rpt_prod.rv_group_product t2
     where t1.group_product_sk = t2.group_product_sk and
           t1.Paid_Date between "&begdate"d and "&enddate"d

group by calculated Paid_Dte
;
quit;