Help using Base SAS procedures

SQL code

Reply
Super Contributor
Super Contributor
Posts: 440

SQL code

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?

Super Contributor
Posts: 1,636

Re: SQL code

create table B as

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

         from table A

             group by date;

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

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 ?

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

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

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

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

Occasional Contributor
Posts: 5

Re: SQL code

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!

Super Contributor
Posts: 543

Re: SQL code

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.

Contributor
Posts: 65

Re: SQL code

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

Frequent Contributor
Posts: 129

Re: SQL code

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

Contributor
Posts: 65

Re: SQL code

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

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

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

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

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

Super User
Super User
Posts: 6,698

Re: SQL code

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.

Super Contributor
Super Contributor
Posts: 440

Re: SQL code

Thanks Tom

Ask a Question
Discussion stats
  • 13 replies
  • 430 views
  • 8 likes
  • 7 in conversation