Proc sql

[vinod4842]

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

[Reeza · Accepted solution]

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]

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]

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]

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]

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]

---

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