Max of variable based on condition

[Scott86]

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

[ChrisNZ · Accepted solution]

Use this simpler syntax:

proc sql; 
  select max(WEIGHT)*(AGE=12)
        ,max(WEIGHT)*(AGE=13)
  from SASHELP.CLASS
  group by AGE;
quit;

[Scott86]

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?

[ChrisNZ]

>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.

[yabwon]

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

[Scott86]

Hi Bart, Yip the sub-query works best but not sure why I needed it over my code. Chris code actually takes the max over all 12 months even for the le 3 and le 6.

Thanks Bart