Help using Base SAS procedures

Proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Proc sql

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


Accepted Solutions
Solution
‎06-20-2017 03:23 AM
Super User
Posts: 19,772

Re: Proc sql

Posted in reply to vinod4842

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 Smiley Happy

View solution in original post


All Replies
Solution
‎06-20-2017 03:23 AM
Super User
Posts: 19,772

Re: Proc sql

Posted in reply to vinod4842

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 Smiley Happy

Occasional Contributor
Posts: 17

Re: Proc sql

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
Super User
Super User
Posts: 7,942

Re: Proc sql

Posted in reply to vinod4842

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.

Occasional Contributor
Posts: 17

Re: Proc sql

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

Super User
Super User
Posts: 7,942

Re: Proc sql

Posted in reply to vinod4842

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;
Super User
Posts: 19,772

Re: Proc sql

Posted in reply to vinod4842

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 256 views
  • 0 likes
  • 3 in conversation