PROC SQL

[aweaver]

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;

[Astounding]

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,

[aweaver]

So I need to do this in a separate data step or can I add that in somewhere
in this statement? Additionally, can you do a rename in the Proc sql
statement?

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[PGStats · Accepted solution]

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;

[aweaver]

When I rename the variables like this is it just temporary or are they permanently renamed as I plan to use them in code after this statement?

Thanks for your help

[RW9]

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?

[aweaver]

Thank you!