topic Re: SQL code in SAS Procedures

SQL code

Tal — Fri, 11 Jan 2013 03:46:08 GMT

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?

Re: SQL code

Linlin — Fri, 11 Jan 2013 03:50:07 GMT

create table B as

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

from table A

group by date;

Re: SQL code

Tal — Fri, 11 Jan 2013 04:03:57 GMT

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 ?

Re: SQL code

Tal — Fri, 11 Jan 2013 04:05:17 GMT

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

Re: SQL code

Tal — Fri, 11 Jan 2013 04:16:03 GMT

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

Re: SQL code

never — Fri, 11 Jan 2013 14:40:16 GMT

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!

Re: SQL code

KarlK — Fri, 11 Jan 2013 14:49:00 GMT

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

Re: SQL code

LarryWorley — Fri, 11 Jan 2013 15:27:41 GMT

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

Re: SQL code

AncaTilea — Fri, 11 Jan 2013 16:27:17 GMT

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.

Re: SQL code

KarlK — Fri, 11 Jan 2013 19:53:53 GMT

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

Re: SQL code

Tal — Sat, 12 Jan 2013 17:53:21 GMT

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

Re: SQL code

Tal — Sun, 13 Jan 2013 22:36:33 GMT

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

Re: SQL code

Tom — Mon, 14 Jan 2013 00:19:27 GMT

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.

Re: SQL code

Tal — Mon, 14 Jan 2013 03:33:58 GMT

Thanks Tom