Hello!!
This is my code:
proc sql;
create table open_adms8 as
select a.*, b.IMPAIRMENT_OTHER as SELF_CARE, b.IMPAIRMENT_TYPE_ID as IMPAIRMENT_ID_1, b.SEVERITY_LEVEL_ID as SEVERITY_LEVEL_1
from open_adms7 a left join self_care b on a.ADMISSION_ID = b.ADMISSION_ID;
quit;
I'm finding that the variables from table B don't adopt the name I've tried assigning to them, they just remain as the original variable names (IMPAIRMENT_OTHER, IMPAIRMENT_TYPE_ID, SEVERITY_LEVEL_ID). Any reason why? I know this would work if I was just selecting and renaming from table A. What can I do?
You might be looking at the variable labels which remain the original ones. To change them, try:
proc sql;
create table open_adms8 as
select a.*, b.IMPAIRMENT_OTHER as SELF_CARE "Self Care",
b.IMPAIRMENT_TYPE_ID as IMPAIRMENT_ID_1 "Impairment Id",
b.SEVERITY_LEVEL_ID as SEVERITY_LEVEL_1 "Severity Level"
from open_adms7 a left join self_care b on a.ADMISSION_ID = b.ADMISSION_ID;
quit;
I ran your code using some tables with overlapping data in sashelp library.
It appears to have run correctly as follows:
proc sql;
create table open_adms8 as
select a.*,
b.REGION as SELF_CARE,
b.PRODUCT as IMPAIRMENT_ID_1,
b.STORES as SEVERITY_LEVEL_1
from sashelp.cars a left join sashelp.shoes b on a.ORIGIN = b.REGION;
quit;
Can you provide your log?
Yes, I also had no issue in running the code, but it doesnt successfully change the variable names. The log is as follows:
864 proc sql;
865 create table open_adms8 as
866 select a.*, b.IMPAIRMENT_OTHER as self_care, b.IMPAIRMENT_TYPE_ID as IMPAIRMENT_ID_1,
866 ! b.SEVERITY_LEVEL_ID as SEVERITY_LEVEL_1
867 from open_adms7 a left join self_care b on a.ADMISSION_ID = b.ADMISSION_ID;
NOTE: Compressing data set WORK.OPEN_ADMS8 decreased size by 97.73 percent.
Compressed is 2 pages; un-compressed would require 88 pages.
NOTE: Table WORK.OPEN_ADMS8 created, with 349 rows and 87 columns.
868 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds
Run a proc contents on open_adms8 immediately after this step, and show us the output.
If your A data set has a variable named IMPAIRMENT_OTHER then that variable will be there and I would expect another variable named self_care to be further the to right in a table view.
Exactly how are you determining that the variables aren't getting renamed?
Note that in this example the A dataset version of age is in the output as well as the renamed age from set B
proc sql; create table work.demo as select a.*, b.age as bage from sashelp.class as a left join sashelp.class as b on a.name=b.name ; run;
Perhaps you just didn't scroll over to the right of the data set far enough.
You might be looking at the variable labels which remain the original ones. To change them, try:
proc sql;
create table open_adms8 as
select a.*, b.IMPAIRMENT_OTHER as SELF_CARE "Self Care",
b.IMPAIRMENT_TYPE_ID as IMPAIRMENT_ID_1 "Impairment Id",
b.SEVERITY_LEVEL_ID as SEVERITY_LEVEL_1 "Severity Level"
from open_adms7 a left join self_care b on a.ADMISSION_ID = b.ADMISSION_ID;
quit;
Yep, I was looking at labels. By tweaking my code with your suggestions it worked out. Thank you so much!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.