topic Re: Rename columns in SAS Programming

Rename columns

Ronein — Tue, 26 Feb 2019 05:40:57 GMT

Hello
How can I rename columns names of was data set by the following way:
Rename 1st column to "Id"
Rename 2nd column to "City"
Rename 3rd column to "country ".
As you can see I don't know the original names so i cannot use Rename=old_name=new_name"

Re: Rename columns

Ronein — Tue, 26 Feb 2019 05:48:00 GMT

The issue is that the original fields names are different every day but the desired fields names is fixed

Re: Rename columns

PeterClemmensen — Tue, 26 Feb 2019 06:04:27 GMT

Do something like this

data have;
var1=''; var2=''; var3='';
run;

proc sql;
   create table want as
   select 1 as Id, 
          2 as City,
          3 as Country
   from have;
quit;

Re: Rename columns

andreas_lds — Tue, 26 Feb 2019 07:13:05 GMT

In sashelp.vcolumn all variables and their position are stored. By querying that view, call execute can be used to code a proc dataset:

data work.class;
   set sashelp.class;
run;

proc format;
   value NewNames
      1 = 'Id'
      2 = 'City'
      3 = 'Country'
   ;
run;

data _null_;
   set sashelp.vcolumn(where=(LibName = 'WORK' and MemName = 'CLASS' and Varnum <= 3)) end=almostDone;

   if _n_ = 1 then do;
      call execute('proc datasets library=work nolist;');
      call execute('modify CLASS;');
   end;

   call execute(catx(' ', 'rename', Name, '=', put(Varnum, NewNames.), ';'));

   if almostDone then do;
      call execute('quit;');
   end;   
run;

Re: Rename columns

RW9 — Tue, 26 Feb 2019 08:25:36 GMT

"As you can see I don't know the original names" - and therein lies your problem. Why do You not know Your data? Where is your import agreement, or data structure agreement - the fundamental basis of using any data is to understand what it contains and how. If you don't know that you may as well go ahead and delete it as it will provide you nothing but work.

Re: Rename columns

Ksharp — Tue, 26 Feb 2019 12:45:18 GMT

data have;
 set sashelp.class;
run;



proc transpose data=have(obs=0) out=temp;
var _all_;
run;
data temp;
 set temp;
 length new $ 40;
 if _n_=1 then new='id    ';
  else if _n_=2 then new='city    ';
   else if _n_=3 then new='country    ';
run;
proc sql noprint;
select catx('=',_name_,new) into : rename separated by ' '
 from temp
  where new is not missing;
quit;

%put &rename ;
proc datasets library=work nolist nodetails;
modify have;
rename &rename ;
quit;

Re: Rename columns

Ronein — Wed, 27 Feb 2019 04:35:40 GMT

This is not a good solution.

Please see the example below.

As you can see in the data sets : want_a want_b want_c , it didn't read the data well

Data a;
input xx1 xx2 xx3;
cards;
1 11 111
2 22 222
3 33 333
;
Run;

Data b;
input yy1 yy2 yy3;
cards;
1 11 111
2 22 222
3 33 333
;
Run;

Data c;
input x1 x2 y3 y4;
cards;
1 11 111 9
2 22 222 9
3 33 333 9
;
Run;


/**************Way1*****************/
/**************Way1*****************/
/**************Way1*****************/
/**************Way1*****************/
proc sql;
   create table want_a as
   select 1 as X1, 
          2 as X2,
          3 as X3
   from a;
quit;

proc sql;
   create table want_b as
   select 1 as X1, 
          2 as X2,
          3 as X3
   from b;
quit;

proc sql;
   create table want_c as
   select 1 as X1, 
          2 as X2,
          3 as X3
   from c;
quit;

Re: Rename columns

Ronein — Wed, 27 Feb 2019 04:48:10 GMT

Great solution!!!