BookmarkSubscribeRSS Feed
vinod4842
Fluorite | Level 6

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 

 

4 REPLIES 4
Reeza
Super User

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

vinod4842
Fluorite | Level 6
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
Reeza
Super User

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

 

Tom
Super User Tom
Super User

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;

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
  • 8369 views
  • 0 likes
  • 3 in conversation