topic Re: Can I rename column 1 without knowing the name of column 1? in SAS Viya

Can I rename column 1 without knowing the name of column 1?

Ryanb2 — Tue, 24 Sep 2024 23:09:28 GMT

I have a lot of datasets with a different name for column 1, but I'd like to rename column 1 using the same name so I can stack them. I can think of ways to do this using macros but I'm curious to know if there's a simpler option. For example, is there a way to rename column 1 (or any column for that matter) using the column order? Something like:

rename=(_COLUMN1_=NEWNAME)

Re: Can I rename column 1 without knowing the name of column 1?

Tom — Wed, 25 Sep 2024 00:15:43 GMT

Figure out what the name is.

Then you can use it to generate the RENAME=() dataset option.

For example say you want to combine the observations from three datasets in the WORK libref . So generate a SET statement that looks like:

set a(rename=(a=column1)) b(rename=(b=column1)) c(rename=(c=column1)) ;

Code:

%let libref=work;
%let dslist=a b c ;
proc sql noprint;
select cats(libname,'.',memname,'(rename=(',name,'=','column1','))')
  into :code separated by ' '
  from dictionary.columns
  where libname=%upcase("&libref")
    and findw("&dslist",memname,,'sir')
    and varnum=1
    and lowcase(name) ne 'column1'
;
quit;
data want;
  set &code ;
run;

PS Can you explain how you got into this situation? Perhaps you can fix the problem earlier in the process and avoid the need to generate RENAME= dataset options.

Re: Can I rename column 1 without knowing the name of column 1?

Ksharp — Wed, 25 Sep 2024 01:09:18 GMT

data have;
 set sashelp.class;
run;



%let dsid=%sysfunc(open(have));
%let first_var=%sysfunc(varname(&dsid,1));
%let dsid=%sysfunc(close(&dsid.));

data want;
 set have(rename=(&first_var=new_name));
run;

Re: Can I rename column 1 without knowing the name of column 1?

FreelanceReinh — Wed, 25 Sep 2024 07:57:04 GMT

Hi @Ryanb2,

You can also use the fact that the UNION operator of PROC SQL (without the CORRESPONDING option) aligns columns by position.

Example:

/* Create sample data for demonstration */

data have1(rename=(name=abc))
     have2(rename=(name=def))
     have3(rename=(name=ghi));
set sashelp.class;
run;

/* Stack HAVE1 - HAVE3, renaming the first column to Firstname */

proc sql nowarn;
create table base (Firstname char(8)); /* define name and length of the first column */

create table want as
select * from base
union all select * from have1
union all select * from have2
union all select * from have3;
quit;

Table BASE may contain more columns to be renamed. PROC SQL would automatically increase the length of character variables to avoid truncation (e.g., if variable DEF in dataset HAVE2 had length 11).

If you have an existing template dataset, you can use that instead:

proc sql;
create table want as
select * from sashelp.class(obs=0)
union all select * from have1
union all select * from have2
union all select * from have3;
quit;

Obviously, you don't need an additional template dataset if the columns in question have the desired names in dataset HAVE1 .