Hi everyone,
I am hoping you can assist me without viewing my data. I am trying to get the max of a variable when a number is lower than a certain figure (the number represents difference in dates). I am getting an error that I cannot figure out tho:
proc sql;
create table data_4 as select customer_id, CLASSIFICATION, EXTR, RISK_SCORE, calendar_month, date,
max(date) as max_date format date9., intck('month', date, CALCULATED max_date) as date_diff, cc,
max(case when CALCULATED date_diff le 3 then cc else 0 end) as Low_cc_l3,
max(case when CALCULATED date_diff le 6 then cc else 0 end) as Low_cc_l6,
max(case when CALCULATED date_diff le 12 then cc else 0 end) as Low_cc_l12
from data_3 group by customer_id order by customer_id, calendar_month;
quit;
The error I am getting is:
ERROR: Summary functions nested in this way are not supported.
ERROR: Summary functions nested in this way are not supported.
ERROR: Summary functions nested in this way are not supported.
I am trying to return the maximum CC in last 3, 6, 12 months.
Any help is appreciated.
Thanks
Use this simpler syntax:
proc sql; select max(WEIGHT)*(AGE=12) ,max(WEIGHT)*(AGE=13) from SASHELP.CLASS group by AGE; quit;
Use this simpler syntax:
proc sql; select max(WEIGHT)*(AGE=12) ,max(WEIGHT)*(AGE=13) from SASHELP.CLASS group by AGE; quit;
That worked thank you. I've never seen SQL SAS used that way. What are the * doing? Also to get last 3 months I had to change le 3 to le 2 and le 6 to le 5 and so on. Why is that?
>What are the * doing?
* is a multiplication. The second expression is a test and returns a Boolean value.
>Also to get last 3 months I had to change le 3 to le 2 and le 6 to le 5 and so on. Why is that?
I don't know what you want to test. Look at the values of DATE_DIFF.
le 2 means 0,1,2 , which means 3 months including the max month.
Hi,
Wouldn't a subquery be a solution?
data data_3;
input customer_id calendar_month date yymmdd10. cc;
format date yymmdd10.;
cards;
1 1 2020-01-01 60
1 2 2020-02-01 50
1 3 2020-03-01 40
1 4 2020-04-01 30
1 5 2020-05-01 20
1 6 2020-06-01 10
2 1 2020-01-01 60
2 2 2020-02-01 50
2 3 2020-03-01 40
2 4 2020-04-01 30
2 5 2020-05-01 20
2 6 2020-06-01 10
;
run;
proc sql;
create table data_4 as
select a.*,
max(case when a.date_diff le 3 then a.cc else 0 end) as Low_cc_l3,
max(case when a.date_diff le 6 then a.cc else 0 end) as Low_cc_l6,
max(case when a.date_diff le 12 then a.cc else 0 end) as Low_cc_l12
from
(
select
customer_id,
calendar_month,
date,
max(date) as max_date format date9.,
intck('month', date, CALCULATED max_date) as date_diff,
cc
from data_3
group by customer_id
) as a
group by customer_id
order by
customer_id,
calendar_month
;
quit;
All the best
Bart
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.