I am trying to pull all the varibales from the subdivisionsort and chartersort data sets yet only keep a couple ultimately but the variable NAME appears in both data sets. I need both of them but I keep getting errors saying that the statement is too ambiguous if I do not specify which data set to pull it from but when I put S. or C. in front of NAME sas says the variable NAME already exists in Realestatefilter. I want to merge them where I am not sure which way is the best way to complete this task but none of the options below are working. Any suggestions would be much appreciated!
Thanks!!
Proc sql;
create table work.Realestatefilter as
select S.NAME,C.NAME,APPROVYEAR,JURISDICTION,YEAROPEN,CITY
from work.Subdivisionsort as S,
work.Chartersort as C
where S.APPROVYEAR=C.YEAROPEN;
quit;
Proc Print data=Realestatefilter;
run;
Proc sql;
create table work.Realestatefilter as
select NAME,APPROVYEAR,JURISDICTION,YEAROPEN,CITY
from work.Subdivisionsort as S,
work.Chartersort as C
where S.APPROVYEAR=C.YEAROPEN;
quit;
Proc Print data=Realestatefilter;
run;
Proc sql;
create table work.Realestatefilter as
select *
from work.Subdivisionsort (keep=NAME APPROVYEAR JURISDICTION) as S,
work.Chartersort (keep= NAME YEAROPEN CITY) as C
where S.APPROVYEAR=C.YEAROPEN;
quit;
Proc Print data=Realestatefilter;
run;
AS can also be used to give a new name to a column or expression :
Proc sql;
create table Realestatefilter as
select
S.NAME as SubName,
C.NAME as CharterName,
APPROVYEAR,
JURISDICTION,
YEAROPEN,
CITY
from
Subdivisionsort as S,
Chartersort as C
where S.APPROVYEAR = C.YEAROPEN;
select * from Realestatefilter;
quit;
In the final SAS data set, they need to have different names. If you want to keep both, you can give them different names, such as:
S.name as SubName, C.name as ChartName,
AS can also be used to give a new name to a column or expression :
Proc sql;
create table Realestatefilter as
select
S.NAME as SubName,
C.NAME as CharterName,
APPROVYEAR,
JURISDICTION,
YEAROPEN,
CITY
from
Subdivisionsort as S,
Chartersort as C
where S.APPROVYEAR = C.YEAROPEN;
select * from Realestatefilter;
quit;
I would really suggest looking up some learning material on SQL, aliasing is a basic fundamental of SQL - in this case the alias becomes a variable.
I would also avoid mixing Base SAS statements in SQL statements. It works, but its neither good coding, nor is is compatible with anything outside SAS - i.e. if you were to connect to a DB, or want to copy the code somewhere else - so this
select *
from work.Subdivisionsort (keep=NAME APPROVYEAR JURISDICTION)
Becomes:
proc sql; create table WORK.REALESTATEFILTER as select S.NAME as S_NAME, coalesce(S.APPROVYEAR,C.YEAROPEN) as YEAR, S.JURISDICTION, C.NAME as C_NAME, C.CITY from WORK.SUBDIVISIONSORT as S, WORK.CHARTERSORT as C where S.APPROVYEAR=C.YEAROPEN; quit;
Note that I coalesce() the joining variables, theres no need to have two variables with the same infomation.
Note also the consistent casing, indentations and such like to make code as readable as possible, and the avoidance of lazy programming techniques like select *. Not sure why you need a proc print after each statement either, it doesn't add anything to the mix as you are creating datasets with each step?
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 25. 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.