hi all,
i have a table A with the following fields date,x,y that i want to group by date and the sum of x and y
table A:
date x y
---------------
12-2-2012 4 5
12-2-2012 6 7
12-5-2012 3 4
12-5-2012 4 7
12-3-2012 2 2
table being grouped:
12-2-2012 10 12
12-5-2012 7 11
12-3-2012 2 2
so when i run the query :select date,sum(x),sum(y) from table A group by date i get the right results.
But when i try to save the results into a new table
(B) i get syntax errors (Incorrect syntax near the keyword 'as'.)
create table B as (select date,sum(x),sum(y) from table A group by date);
and i am running this on SQL Microsoft Studio 2008
What i am doing wrong here,anyone please?
create table B as
select date, sum(x) as xx,sum(y) as yy
from table A
group by date;
thanks for the quick response Linlin but i did run the same code w/o the brackets after "as" and before ; and i still get the error.Is "table" from "from table A" really necessary ?
i mean i have "table" in my own query too but i was wondering if it s necessary
never mind the last question.I must have ran the first query without the "table" from "from table A" otherwise it wont run but i still cannot run the second query
I'm not 100% sure here, but I never use SAS SQL code on other SQL platforms.
To be more precise, in SAS SQL you can create tables "on the fly" by using the "create table foo as ..." syntax but I think this is only a convenient SAS structure which will not translate over to other SQL platforms like microsoft visual studio (although it may for some, I don't know).
Although I thought this was a SAS forum, I will propose the following solution because the syntax below is also allowed in SAS SQL although sometimes unnecessary:
FIrst create a blank shell for your table using:
create table B
(
date as date,
xx as float,
yy as float
) ;
Notice that there is no "as" after the create statement!
Then insert into that table the values you want:
insert into B
select date, sum(x) as xx,sum(y) as yy
from table A
group by date ;
You should note that some things may change depending on the syntax of SQL in microsoft visual studio... For instance the the round parenthases, (), may be curly brackets, {}.And the declaration of "date as date" may require a different data type. I haven't used SQL in visual studio so I can't give you any exact code, but I think this will get you on your way...
Message was edited by: never aragafi Notice that there is no "as" after the create statement!
Tal,
What LinLin has written is correct (and everyone else).
If you want to create a table from SQL you need to tell SAs to save sum(x) as sum_X and sum(y) as sum_Y
I am just making sure you noticed 'extra' ASes.
Best of luck.
Anca.
Did I understand you to say you're not using SAS proc sql? "create table whatever as. . ." is SAS syntax, but is not ANSI standard SQL. To create a new table with a select statement in most SQL dialects, you use "select into", e.g.,
select <your columns> into B
from A
group by date;
Which will create a new table b in the default database.
Karl
Tal,
ANSI standard does allow the syntax of
create table new_table_name as
select column_spec1,....,,
from table_name
group by group_by_column_spec
;
The issue with your query (as you suspected) is the word 'table' in the from clause 'from table a'. That clause should simply be 'from a'
Larry
Yeah, you're right, Larry. The OP mentioned MS Visual Studio, which I assumed sits on top of SQL Server, so I cranked up my SQL SRV 2012 and "create table as. . ." throws an error. You have to use "select . . . into . . .". That apparently is a MS quirk which I mistakenly took to be ANSI--always risky with MS products.
Thanks for schooling me.
Karl
actually Karl,i was running this queries on sql developer /oracle and the "create table as ..." worked perfectly for me.Probably the issue was with sum(x),sum(y.I was forgetting the sum(x) as... or sum (y) as.. So thanks guys,thank you all
i want to add another question to this
if let's say i need to calculate the total sum of sum(x) and sum (y) of the table below can i achieve it with the same query or do i need to run another one?
12-2-2012 10 12
12-5-2012 7 11
12-3-2012 2 2
total: 19 25
Easy to do in SAS. Just use proc print; var date x y; sum x y; run;
In SQL it is harder because you need to create a table instead of a report.
(select date,x,y from have)
union
(select . as date,sum(x) as x, sum(y) as y from have group by date)
To get the date and the label "TOTAL" to appear in the same variable it would need to be character.
Thanks Tom
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.