topic Re: sas in SAS Programming

SQL Error - Summary functions nested are not supported

vinod4842 — Sun, 16 Jul 2017 07:49:03 GMT

835 proc sql;
836 create table cadd as select 'ARVIND CHATURVEDI Total' as SH_NAME,sum(INWARDDAY)as INWARDDAY,
837 sum(INWARDLMON) as INWARDLMON,sum(d1) as d1, sum(NCADAY) as NCADAY,sum(NCAMON) as NCAMON,
837! sum(NCALMON) as NCALMON,
838 sum(d2) as d2, 5 as sno from work.c where team=1
839 union all
840 select 'SAROJ SAMAL Total' as TM_NAME,sum(sum(INWARDDAY))as INWARDDAY,sum(sum(INWARDLMON)) as
840! INWARDLMON,
841 sum(sum(d1)) as d1, sum(sum(NCADAY)) as NCADAY,sum(sum(NCAMON)) as NCAMON, sum(sum(NCALMON)) as
841! NCALMON,sum(sum(d2)) as d2 ,5 as sno
842 from work.c where team=1;
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.
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.
ERROR: Summary functions nested in this way are not supported.
843 run;

it showing error

Re: sas

Reeza — Sun, 16 Jul 2017 07:48:40 GMT

@vinod4842 wrote:

835 proc sql;
836 create table cadd as select 'ARVIND CHATURVEDI Total' as SH_NAME,sum(INWARDDAY)as INWARDDAY,
837 sum(INWARDLMON) as INWARDLMON,sum(d1) as d1, sum(NCADAY) as NCADAY,sum(NCAMON) as NCAMON,
837! sum(NCALMON) as NCALMON,
838 sum(d2) as d2, 5 as sno from work.c where team=1
839 union all
840 select 'SAROJ SAMAL Total' as TM_NAME,sum(sum(INWARDDAY))as INWARDDAY,sum(sum(INWARDLMON)) as
840! INWARDLMON,
841 sum(sum(d1)) as d1, sum(sum(NCADAY)) as NCADAY,sum(sum(NCAMON)) as NCAMON, sum(sum(NCALMON)) as
841! NCALMON,sum(sum(d2)) as d2 ,5 as sno
842 from work.c where team=1;
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.
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.
ERROR: Summary functions nested in this way are not supported.
843 run;

it showing error

You have invalid syntax and I've highlighted the cause of the errors. What are you trying to do? Post sample data and expected output. And you know, maybe a short description of what the issue is besides 'is showing error'. That doesn't provide any information.

Re: SQL Error - Summary functions nested are not supported

vinod4842 — Sun, 16 Jul 2017 08:05:57 GMT

id name age sub1 sub2 sub3
1 abc 13 60 50 60
2 cde 13 70 50 66
3 def 14 66 71 63
4 poe 15 99 36 41
5 wec 16 44 66 65
;
run;

expecting result;
id name age sub1 sub2 sub3;
1 abc 13 60 50 60
2 cde 13 70 50 66
sub tot 130 100 126
tot 130 100 126
3 def 14 66 71 63
4 poe 15 99 36 41
sub tot 165 107 104
tol 295 207 230
5 wec 16 44 66 65
sub tot 44 66 65
tot 44 66 65
grand tot 339 273 295

Re: SQL Error - Summary functions nested are not supported

Reeza — Sun, 16 Jul 2017 08:16:43 GMT

If this is for display use PROC REPORT. If for creating a summary table, look at PROC MEANS with the MULTILABEL format option.

@vinod4842 wrote:
id name age sub1 sub2 sub3
1 abc 13 60 50 60
2 cde 13 70 50 66
3 def 14 66 71 63
4 poe 15 99 36 41
5 wec 16 44 66 65
;
run;

expecting result;
id name age sub1 sub2 sub3;
1 abc 13 60 50 60
2 cde 13 70 50 66
sub tot 130 100 126
tot 130 100 126
3 def 14 66 71 63
4 poe 15 99 36 41
sub tot 165 107 104
tol 295 207 230
5 wec 16 44 66 65
sub tot 44 66 65
tot 44 66 65
grand tot 339 273 295

Re: SQL Error - Summary functions nested are not supported

Tom — Mon, 17 Jul 2017 00:29:28 GMT

You don't seem to have any grouping variables in your example data that would allow for calculating subtotals and totals. So first let's just make some so we have something to demonstrate with.

data expect ;
  input group subgroup id name :$20. age sub1 sub2 sub3;
cards;
1 1 1 abc 13 60 50 60
1 1 2 cde 13 70 50 66
1 1 . sub_total . 130 100 126
1 . . total . 130 100 126
2 1 3 def 14 66 71 63
2 1 4 poe 15 99 36 41
2 1 . sub_total . 165 107 104
2 . . total . 295 207 230
3 1 5 wec 16 44 66 65
3 1 . sub_total . 44 66 65
3 . . total . 44 66 65
. . . grand_total . 339 273 295
;

Now we can just throw out the summary lines to get some example INPUT data that we can use the test our program.

data have ;
  set expect ;
  where age^=. ;
run;

Now if you want to do this with PROC SQL then just UNION the queries that create the different types of rows. It would be nice if we could make the output sort into the order that you presented. For now I will just use some arbitraryly large values for the GROUP, SUBGROUP and ID variables to make that work.

proc sql ;
  create table want as
  select * from have
  union
  select group,subgroup,9999 as id,'sub_total' as name,. as age
       , sum(sub1) as sub1
       , sum(sub2) as sub2
       , sum(sub3) as sub3
  from have group by group,subgroup
  union
  select group,9999 as subgroup,9999 as id,'total' as name,. as age
       , sum(sub1) as sub1
       , sum(sub2) as sub2
       , sum(sub3) as sub3
  from have group by group
  union
  select 9999 as group,9999 as subgroup,9999 as id,'grand_total' as name,. as age
       , sum(sub1) as sub1
       , sum(sub2) as sub2
       , sum(sub3) as sub3
  from have
  ;
quit;