Rename columns

[Ronein]

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"

[Ronein]

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

[PeterClemmensen]

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;

[Ronein]

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;

[andreas_lds]

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;

[RW9]

"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.

[Ksharp · Accepted solution]

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;

[Ronein]

Great solution!!!