BookmarkSubscribeRSS Feed
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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?

13 REPLIES 13
Linlin
Lapis Lazuli | Level 10

create table B as

     select date, sum(x) as xx,sum(y)  as yy

         from table A

             group by date;

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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 ?

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

i mean i have "table" in my own query too but i was wondering if it s  necessary

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

never
Calcite | Level 5

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!

AncaTilea
Pyrite | Level 9

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.

KarlK
Fluorite | Level 6

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

LarryWorley
Fluorite | Level 6

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

KarlK
Fluorite | Level 6

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

i want to add another question to this Smiley Happy

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

Tom
Super User Tom
Super User

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.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

Thanks Tom

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1436 views
  • 8 likes
  • 7 in conversation