topic Re: Proc sql in SAS Procedures

Proc sql

vinod4842 — Tue, 20 Jun 2017 05:48:56 GMT

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

Re: Proc sql

Reeza — Tue, 20 Jun 2017 05:53:36 GMT

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 🙂

Re: Proc sql

vinod4842 — Tue, 20 Jun 2017 07:23:40 GMT

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

Re: Proc sql

RW9 — Tue, 20 Jun 2017 08:02:33 GMT

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.

Re: Proc sql

vinod4842 — Tue, 20 Jun 2017 11:16:42 GMT

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

Re: Proc sql

RW9 — Tue, 20 Jun 2017 12:05:45 GMT

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;

Re: Proc sql

Reeza — Tue, 20 Jun 2017 13:55:13 GMT

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