- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.