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
Dataset option should follow dataset and not the alias 🙂
Costing_1 (rename=(CIHI_KEY=key2)) as b
"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.
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
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;
Nice idea 🙂
Dataset option should follow dataset and not the alias 🙂
Costing_1 (rename=(CIHI_KEY=key2)) as b
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: