BookmarkSubscribeRSS Feed
Alexey
Calcite | Level 5

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Alexey
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Kurt_Bremser
Super User

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

Alexey
Calcite | Level 5

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.

Kurt_Bremser
Super User

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?

cwilson
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2528 views
  • 0 likes
  • 4 in conversation