SQL

[Ranjeeta]

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
 

[novinosrin]

"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. 

[Ranjeeta]

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

[novinosrin]

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

[Reeza]

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.

[Ranjeeta]

Thankyou

[mkeintz]

@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;

[novinosrin]

Nice idea 🙂

[Ranjeeta]

Thankyou !!

[novinosrin]

@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. 

[Ranjeeta]

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;

[novinosrin · Accepted solution]

Dataset option should follow dataset and not the alias 🙂

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

[Ranjeeta]

Many thanks 🙂