topic Re: SQL in SAS Programming

SQL

Ranjeeta — Mon, 03 Dec 2018 18:03:29 GMT

proc sql;
488! create table NACRS_AA_COSTsql as
489 select *
490 from NACRS_AA_1 as a, Costing_1 as b
491 where a.CIHI_KEY=b.CIHI_KEY
492 ;
WARNING: Variable CIHI_KEY already exists on file WORK.NACRS_AA_COSTSQL.
NOTE: Table WORK.NACRS_AA_COSTSQL created, with 1171 rows and 81 columns.

493 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.39 seconds
cpu time 0.39 seconds

Hello Can someone please advise why I get the warning message

Re: SQL

novinosrin — Mon, 03 Dec 2018 18:17:44 GMT

"Hello Can someone please advise why I get the warning message" -- this is because of the ambiguity for the sql processor to determine which CIHI_KEY for select clause to pick as that column is present in both your tables referred in from clause.

http://support.sas.com/kb/9/963.html

Also, try to avoid using * in production code as far as possible.

Re: SQL

Ranjeeta — Mon, 03 Dec 2018 18:20:05 GMT

thankyou in this case the ihikey is picked from which table ?
Alsohw would i specify to select cihikey from one table but i also need all the columns from both tables
I dont have training in sql so asking basic q's

Re: SQL

novinosrin — Mon, 03 Dec 2018 18:26:09 GMT

The column that is kept is from the first table in the from clause that contains that variable.

Unfortunately, you will have to type like

select a.col1,a.col2,....a.coln , b.col,b.coln

Do not specify the key variable in b. series of col names

Re: SQL

Reeza — Mon, 03 Dec 2018 18:29:00 GMT

Try the FEEDBACK option. Add it to the PROC SQL statement and then check your log. You can copy the query and use that instead.

Proc sql feedback;
Create table want as
Select *
From sashelp.class as a, sashelp.class as b
Where a.name=b.name;
Quit;

This is a useless query but should illustrate your issue.

Re: SQL

mkeintz — Mon, 03 Dec 2018 19:38:44 GMT

@novinosrin:

But you CAN avoid extensive variable lists and preserve usage of "select *" with judicious use of the data set name parameters RENAME and DROP.:

proc sql;
 create table NACRS_AA_COSTsql (drop=key2) as
 select *
   from NACRS_AA_1 as a, Costing_1 (rename=(cihi_key=key2)) as b
   where a.CIHI_KEY=b.KEY2
   ;
quit;

Re: SQL

novinosrin — Mon, 03 Dec 2018 19:39:35 GMT

Nice idea 🙂

Re: SQL

novinosrin — Mon, 03 Dec 2018 19:47:44 GMT

@Ranjeeta To what you wrote as basic, doesn't seem basic anymore considering you take a look at @mkeintzMark's idea, i.e slick use of dataset options and elegantly keeping * to select all. I hope you took note of that as I did.

Re: SQL

Ranjeeta — Mon, 03 Dec 2018 21:05:37 GMT

thankyou
I am getting an error
proc sql;
102! create table NACRS_AA_COSTsql (drop=key2) as
103 select *
104 from NACRS_AA_1 as a, Costing_1 as b (rename=(CIHI_KEY=key2))
-
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.

105 where a.CIHI_KEY=b.key2
106 ;
107 quit;

Re: SQL

novinosrin — Mon, 03 Dec 2018 21:28:00 GMT

Dataset option should follow dataset and not the alias 🙂

Costing_1 (rename=(CIHI_KEY=key2)) as b

Re: SQL

Ranjeeta — Mon, 03 Dec 2018 21:36:42 GMT

Many thanks 🙂

Re: SQL

Ranjeeta — Mon, 03 Dec 2018 21:36:58 GMT

Thankyou !!

Re: SQL

Ranjeeta — Mon, 03 Dec 2018 21:37:08 GMT

Thankyou