Rename columns

[Tal]

Hello,

I receive a table beginning of each month  out of which i create a   master dataset (append each table to the previous month one)
but the  problem is one of the  field's name  in  that table  changes every time  a  new  table  arrives and so does the format of that column. For example last  month  the dataset  had  these columns :var1 var2  var3 where var2 has numeric values and  this month the   headers were var1 var2a var3  and  var2a had char values so  I always have to manually change the name and  convert the value of  the second field  before appending  the new  table and  there is no pattern as  to how they  named  that variable so next month it  could be  var1, varabc,var3

I wanted to automate this process  so the conversion of the second  column and  the appending is done with only pushing   a  button  but   my  confusion is  about that  second  header. Is it possible that SAS picks only the second  field  of  a  certain  dataset and  names  it lets say var2  without  worrying  what the original name of that field is?

Any  suggestions appreciated 🙂

Thx

[ballardw]

---

@Tal wrote:

Hello,

I receive a table beginning of each month  out of which i create a   master dataset (append each table to the previous month one)
but the  problem is one of the  field's name  in  that table  changes every time  a  new  table  arrives and so does the format of that column. For example last  month  the dataset  had  these columns :var1 var2  var3 where var2 has numeric values and  this month the   headers were var1 var2a var3  and  var2a had char values so  I always have to manually change the name and  convert the value of  the second field  before appending  the new  table and  there is no pattern as  to how they  named  that variable so next month it  could be  var1, varabc,var3

I wanted to automate this process  so the conversion of the second  column and  the appending is done with only pushing   a  button  but   my  confusion is  about that  second  header. Is it possible that SAS picks only the second  field  of  a  certain  dataset and  names  it lets say var2  without  worrying  what the original name of that field is?

Any  suggestions appreciated 🙂

Thx

---

How are you reading the data into SAS?

If your are relying on Proc Import this one reason not to.

Use a data step to read the data and as long as the column values behave the column name, length, format and informat are as you assign them in the data step. Once you have code to read one file then all you need to do is change the name of the input file and possibly the name of the output data set from the data step. The changed data set name wouldn't likely be needed if you are appending to an existing data set.

If your data is some sort of text file and proc import "works" to read it, then SAS wrote a data step for you. Copy it from the log and edit to the names and properties you need.

[Tal]

Data is actually  coming in excel (three tabs, 4  tabs,depends) and  i  use  pcfiles (libname mylib pcfiles.....)to import it into SAS. The tabs are named  differently  every time i  receive  this excel creating  different datasets  after the import but  i have a  macro  that  takes care of it (picking all  the  datasets in my  library and  appending  them) 

Now the  problem is before running  that macro  some times i  have to convert the value  of the  second  field and  conversion not  a  problem  as long as  that header does not keep changing its name.

The data  in the  excel  tabs is kinda messy(some titles on the  top rows which are always in the second  column  of the sheets),then empty  rows and then  data) so when  i  try  importing  it that title behaves as  the  2nd  header in my  tables and  that title  is always different in each  tab, every month 

The rest of the  headers  are ok, always F1,F3,.......F23. It is the second one  that messes  up and  prevents me  from  automating  this so i was hoping SAS is capable of calling  the  fields in  the datasets using  indexes