topic Re: Rename Column in SAS Programming

Rename Column

Alexey — Thu, 23 Oct 2014 08:16:05 GMT

Hello, i would like to rename a column in data set.

example : i want to rename a column which name is &B_AccountNumber for Account.

the problem is that the name in macro variable &B_AccountNumber composed of two words(with blank, for example : &B_AccountNumber=Account Number). if &B_AccountNumber is composed from one word, algorithm works perfectly.

data Balance_update;

set Balance (rename=(&B_AccountNumber=Account));

run;

What should i do?

i tried to write (rename=('&B_AccountNumber'n=Account)) but is not work!

Thank you

Alexey

Re: Rename Column

RW9 — Thu, 23 Oct 2014 08:33:37 GMT

Hi,

I would firstly question why you want to do this in this fashion, there are many possibilities for mistakes. Perhaps provide an example and it could be done differently. As for your issue with blanks, not sure where B_accountnumber is created, I would suggest at that point would be the best to process the text into a useable format

data _null_;
call symput('b_accountnumber',tranwrd("my model"," ","_"));
run;

data Balance_update;
set sashelp.cars (rename=(make=&B_AccountNumber.));
run;

Re: Rename Column

Kurt_Bremser — Thu, 23 Oct 2014 08:34:07 GMT

Use "&B_AccountNumber"n.

Note the double quotes. Single quotes prevent the handling of macro elements.

Re: Rename Column

Kurt_Bremser — Thu, 23 Oct 2014 08:36:09 GMT

Further question: what causes the column in Balance to have a name that does not meet the SAS requirements for a column/variable name?

Re: Rename Column

Alexey — Thu, 23 Oct 2014 09:13:36 GMT

Hello, thank you for your answer.

I import excel files, and in every excel i have different variable name. my aim is convert every variable name to unique variable name that will be identical across different files.

the next problem that the names of variable is in Hebrew... Sometimes they consist from two words with blank.

Re: Rename Column

Alexey — Thu, 23 Oct 2014 09:14:49 GMT

Hello, thank you for your answer.

I import excel files, and in every excel i have different variable name. my aim is convert every variable name to unique variable name that will be identical across different files.

the next problem that the names of variable is in Hebrew... Sometimes they consist from two words with blank, sometimes, one word, sometimes three words.

Re: Rename Column

RW9 — Thu, 23 Oct 2014 12:03:03 GMT

Why not just do:

proc import datafile='....xls' out=temp replace;

getnames=no;

range='A1:...'; /* not sure if datarows=1 works here */

run;

and let proc import assign col1-colx to each variable?

Re: Rename Column

cwilson — Thu, 23 Oct 2014 13:43:37 GMT

You don't mention where you get the contents for your macro variable, so I will assume that you have multiple sources for the excel files you want to import.

In source A, account number is the third column, in source B, account number is the 5th column, etc. So you have predefined macro variables:

%LET A_AccountNumber = AccountNum;

%LET B_AccountNumber = Customer Account;

%LET C_AccountNumber = Cust. Nbr.;

BUT we know that when SAS imports the excel file, it will convert all spaces and punctuation to underscores, so your variable names become:

%LET A_AccountNumber = AccountNum;

%LET B_AccountNumber = Customer_Account;

%LET C_AccountNumber = Cust__Nbr_;

IF you are already predefining, then you can take care of this right away.

Re: Rename Column

Kurt_Bremser — Thu, 23 Oct 2014 13:59:18 GMT

If the variables are actually "named" in Hebrew, then you will have to consider if SAS is able to handle DBCS at that level (syntax objects instead of just data) before renaming.

I used the double quotes because in Excel these are not names, just data in the first row.

You may be better off by

- exporting excel content into csv

- write a data step manually to import, starting in row 2.

That way you discard the different names for identical columns

BTW, did you try the double quotes?