BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vinod4842
Fluorite | Level 6

PROC SQL;
CREATE TABLE WORK.NRI AS SELECT 'TOTAL' AS COUNTRY, SUM(ACCOUNTS_OPENED_LAST_MONTH) as ACCOUNTS_OPENED_LAST_MONTH,
sum(C2T_till_date_for_last_month) as C2T_till_date_for_last_month, (sum(C2T_till_date_for_last_month)/sum(ACCOUNTS_OPENED_LAST_MONTH))*100,
sum(Accounts_current_year) as Accounts_current_year, sum(Accounts_C2T_current_year) as Accounts_C2T_current_year,
(sum(Accounts_C2T_current_year)/sum(Accounts_current_year))*100, sum(New_Traders_today) as New_Traders_today,
sum(New_Traders_current_month) as New_Traders_current_month, 12 as sno from NRI_team,
group by( 1-sec , IBG )from Nri_team;

quit;

 

169 PROC SQL;
170 CREATE TABLE WORK.NRI AS SELECT 'TOTAL' AS COUNTRY, SUM(ACCOUNTS_OPENED_LAST_MONTH) as
170! ACCOUNTS_OPENED_LAST_MONTH,
171 sum(C2T_till_date_for_last_month) as C2T_till_date_for_last_month,
171! (sum(C2T_till_date_for_last_month)/sum(ACCOUNTS_OPENED_LAST_MONTH))*100,
172 sum(Accounts_current_year) as Accounts_current_year, sum(Accounts_C2T_current_year) as
172! Accounts_C2T_current_year,
173 (sum(Accounts_C2T_current_year)/sum(Accounts_current_year))*100, sum(New_Traders_today) as
173! New_Traders_today,
174 sum(New_Traders_current_month) as New_Traders_current_month, 12 as sno from NRI_team,
175 group by( 1-sec , IBG )from Nri_team;
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

176
177 quit;

it showig error ..for group by statement.

 

plz rectify

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Remove the comma before the word GROUP. 

 

You should really really format your code. If makes it easier to see your errors. 

If you're too lazy to do it yourself, highlight your code and press CTRL+ i in SAS EG

or click the format button in SAS Studio. This is what I do 🙂

View solution in original post

6 REPLIES 6
Reeza
Super User

Remove the comma before the word GROUP. 

 

You should really really format your code. If makes it easier to see your errors. 

If you're too lazy to do it yourself, highlight your code and press CTRL+ i in SAS EG

or click the format button in SAS Studio. This is what I do 🙂

vinod4842
Fluorite | Level 6
data ds;
infile datalines;
input id name$ age sex$ sal company$;
datalines;
111 abc 22 m 20000 infosys
121 xyz 32 m 21002 omega
131 def 63 m 41200 symprony
132 ekd 41 f 74100 infosys
474 ike 36 f 41200 omega
412 ier 36 f 32011 omega
;
run;

proc sql;
create table ds1 as select 'total' as id,name as name, sum(sal) as sal ,group by company from ds ;
run;
from this i want the company sub total and grand total in proc sql help me
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its generally a good idea to start a new thread for new questions.

You would need a union (or join if you want total on each row) as you can only group once in a select:

data ds;
  infile datalines;
  input id name $ age sex $ sal company $;
datalines;
111 abc 22 m 20000 infosys
121 xyz 32 m 21002 omega
131 def 63 m 41200 symprony
132 ekd 41 f 74100 infosys
474 ike 36 f 41200 omega
412 ier 36 f 32011 omega
;
run;

proc sql;
  create table WANT as
  select  COMPANY,
          count(*) as TOTAL
  from    DS
  group by COMPANY
  union all 
  select  "Total",
          count(*) as TOTAL
  from    DS;
quit;

Note the use of the code window (its the {i} above the post area) this retains code formatting which makes reading code easier.

vinod4842
Fluorite | Level 6

thank you very much but i am looking for the result of subtotal 'infosys total' , 'omega total' i,...........under the 'id' variable and sal subtotal  with other observation in and also grand total for this i am not getting..plz rectify

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please specify what the output should look like then, save, me trying to to guess.  If you want the names updated then something like:

data ds;
  infile datalines;
  input id name $ age sex $ sal company $;
datalines;
111 abc 22 m 20000 infosys
121 xyz 32 m 21002 omega
131 def 63 m 41200 symprony
132 ekd 41 f 74100 infosys
474 ike 36 f 41200 omega
412 ier 36 f 32011 omega
;
run;

proc sql;
  create table WANT as
  select  cats(COMPANY," Total") as COMPANY,
          count(*) as TOTAL
  from    DS
  group by COMPANY
  union all 
  select  "Total",
          count(*) as TOTAL
  from    DS;
quit;
Reeza
Super User

@vinod4842 wrote:

thank you very much but i am looking for the result of subtotal 'infosys total' , 'omega total' i,...........under the 'id' variable and sal subtotal  with other observation in and also grand total for this i am not getting..plz rectify


Use a reporting procedure such as PROC REPORT not SQL for this. 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1124 views
  • 0 likes
  • 3 in conversation