proc sql left join rename variables from second table

[accintron]

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?

[edo333]

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? 

[accintron]

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

[Kurt_Bremser]

Run a proc contents on open_adms8 immediately after this step, and show us the output.

[ballardw]

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.

[PGStats · Accepted solution]

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;

[accintron]

Yep, I was looking at labels. By tweaking my code with your suggestions it worked out. Thank you so much!