topic Re: Rename columns in SAS Procedures

Rename columns

Tal — Thu, 09 May 2019 20:44:57 GMT

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

Re: Rename columns

ballardw — Thu, 09 May 2019 21:17:33 GMT

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

Re: Rename columns

Tal — Fri, 10 May 2019 19:50:30 GMT

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