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;
I'm not sure why your PROC SQL doesn't work, but unless we see the input data, its hard to diagnose.
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
;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.