topic Re: Renaming Colums in PROC SQL in SAS Programming

Renaming Colums in PROC SQL

unwashedhelimix — Mon, 02 Dec 2024 23:09:43 GMT

So, I have some data that looks like this:

data product_data;
    input prodnum $ 1-4 prodname $ 6-27 manunum $ 29-31 prodtype $ 33-43 rtlcost 45-49; 
	format rtlcost dollar8.2;
    cards;
5119 Dream Machine          500 Workstation 3200 
4216 Business Machine       450 Workstation 3215 
5112 Office Phone           560 Phone       172 
3110 Spreadsheet Software   134 Software    300 
1230 Database Software      113 Software    757 
3431 Statistical Software   243 Software    1789 
2102 Wordprocessor Software 245 Software    423 
;
run;

I am running proc sql to apply price increase/decrease to some of the products:

proc sql;
    /* 20% price increase for all Software products */
    update product_data
    set rtlcost = rtlcost * 1.20
    where prodtype = 'Software';

    /* 20% price decrease for all other products */
    update product_data
    set rtlcost = rtlcost * 0.80
    where prodtype not like 'Software';
quit;

proc print data=product_data noobs;
run;

Is there a way to rename the column names in proc sql, and NOT in proc print or the data step? Like for example, renaming "prodnum" as Product Number, "prodname" as Product Name, "manunum" as Manufacturer Number, etc.

Re: Renaming Colums in PROC SQL

Kurt_Bremser — Mon, 02 Dec 2024 23:06:03 GMT

Use a LABEL statement in a DATA step, or a LABEL= option in SQL to assign pretty strings to variables for display.

Re: Renaming Colums in PROC SQL

Ksharp — Tue, 03 Dec 2024 01:42:08 GMT

You could create a new table to override the original one.

data have;
 set sashelp.class;
run;


proc sql undopolicy=none;
create table have as
select name as Eng_Name,sex as Female_Male   ,age,weight,height
 from have;
quit;


proc sql undopolicy=none;
create table have as
select *
 from have(rename=(name=Eng_Name sex=Female_Male));
quit;

data have;
 set sashelp.class;
run;


proc sql ;
create table temp as
select *
 from have(rename=(name=Eng_Name sex=Female_Male));

create table have as select * from temp;
quit;

Or you could create a VIEW for this purpose, which don't occupy the size of storage.


proc sql ;
create view have2 as
select name as Eng_Name,sex as Female_Male   ,age,weight,height
 from have;
quit;

Re: Renaming Colums in PROC SQL

unwashedhelimix — Tue, 03 Dec 2024 02:22:13 GMT

How can I input nicer strings? I tried this:

proc sql;

	create table temp as
	select *
	from product_data(rename=(prodnum='Product Number' prodname='Product Name'));

	create table product_data as select * from temp;
quit;

title "Product Information";
proc print data=temp noobs;
run;

but it doesn't rename the column headings. It works when I do something like prodnum=Product_Number, but I want spaces rather than underscores.

Re: Renaming Colums in PROC SQL

Ksharp — Tue, 03 Dec 2024 02:37:53 GMT

Your PROC PRINT is not right should be

proc print data=product_data noobs;
run;

And using system option VALIDVARNAME=ANY to make it happend. Here is an example:

data have;
 set sashelp.class;
run;


options validvarname=any;
proc sql ;
create table temp as
select * from have;

create table have as 
 select * 
 from temp(rename=(name="Eng Name"n  sex="Female Male"n  age='age  age'n));
quit;
proc print data=have noobs;run;

Re: Renaming Colums in PROC SQL

Tom — Tue, 03 Dec 2024 03:34:57 GMT

If you want to name the variables using strings that are not valid SAS names you have to first set the VALIDVARNAME option to ANY.

options validvarname=any;

After that you will have to use NAME LITERALS in your code to create or reference any name that is not a normal valid SAS name.

Your example SQL code does not have any steps that would make it possible to rename the variables, since all you are showing are UPDATE statements.

But in other more normal SQL code you could rename the variables when you list them in the SELECT clause of your CREATE TABLE statement.

create table want as
  select prodnum as 'Product Number'n
       , prodname as 'Product Name'n
       , manunum as 'Manufacturer Number'n
...

Or use the RENAME= dataset option on either the output dataset reference

create table want(rename=
        (prodnum='Product Number'n
         prodname='Product Name'n
         manunum='Manufacturer Number'n ....))
  as select ....

Or rename them on the way IN (remember to use the new names in the code)

... from have(rename=(....)) ...

Not sure why you are using SQL code for this problem however.

It would much simpler in normal SAS code.

data want;
  set product_data;
  if (prodtype = 'Software') then rtlcost = rtlcost * 1.20;
  else rtlcost = rtlcost * 0.80;
run;

But if you are going to use the UPDATE statement of PROC SQL your example can be done in one STEP by using a CASE clause to select the right cost adjustment factor for each observation.

update product_data
set rtlcost = rtlcost * case when (prodtype = 'Software') then 1.20 else 0.80 end;

Re: Renaming Colums in PROC SQL

Tom — Tue, 03 Dec 2024 03:36:59 GMT

"Column Headings"?

Are you just looking for how to attach LABELS to your variables?

proc print data=product_data label noobs;
label prodnum='Product Number' prodname='Product Name';
run;

Re: Renaming Colums in PROC SQL

Kurt_Bremser — Tue, 03 Dec 2024 11:28:16 GMT

Do not saddle yourself with those stupid name literals. Labels are made for pretty strings, so use them, but keep variable names simple for easier coding.