DATA Step, Macro, Functions and more

Aggregrate Date and Total

Reply
Occasional Contributor
Posts: 11

Aggregrate Date and Total

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;

Trusted Advisor
Posts: 1,913

Re: Aggregrate Date and Total

Posted in reply to nupedoc79

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

Super User
Super User
Posts: 7,042

Re: Aggregrate Date and Total

Posted in reply to nupedoc79

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 
  ;
Respected Advisor
Posts: 4,920

Re: Aggregrate Date and Total

Posted in reply to nupedoc79

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.

PG
Occasional Contributor
Posts: 11

Re: Aggregrate Date and Total

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;

 

Ask a Question
Discussion stats
  • 4 replies
  • 506 views
  • 0 likes
  • 4 in conversation