BookmarkSubscribeRSS Feed
nupedoc79
Fluorite | Level 6

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;

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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 
  ;
PGStats
Opal | Level 21

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
nupedoc79
Fluorite | Level 6

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;

 

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
  • 4 replies
  • 1244 views
  • 0 likes
  • 4 in conversation