How to change/remove the name of a repeating column in sas

[A-junamu_-]

I have two datasets that I would like to merge together, but each datasets have a series of columns that need to be sorted, renamed or trimmed. I have a column "top" that repeats several times with additional numbers and I'm trying to crop out the following sequential characters or extract just "to" from all columns itself to later remove it.

My goal is to remove the character "to" from appearing in the final data, but I tried doing that directly and it did not work. 

For example,

Dataset 1 has " 

Top1

Top2

Top4....

Dataset 2 has: 

Tops23

Tops45

Topsi46...

Here are some codes I was thinking of using:

Proc sort data = first_dataset ;

by id;

run;

data first_dataset_clean;

set first_dataset ;

by id;

to = substr(ex:, 1, 3);

run;

data first_dataset_clean second_dataset_clean;

merge first_dataset(rename = ("top:" = "to")) second_dataset (rename = ("top:" = "to"))

by id;

if first.id = 1 and last.id =1 then output first_dataset_clean;

else output second_dataset_clean;

run;

I've been considering using "where ne", "scan", "tranwrd", "substr", "rename" but couldn't figure out how to pull out just "to" from both datasets. 

Any thoughts?

[ballardw]

I am a little confused about some details. Do you have any values that do not start with "to"? If so you should include some. Rename implies VARIABLES. Which cannot actually duplicate in a data set. And you cannot use string functions on the name of a variable, or at least not without a fair amount of work. It looks like you are attempting to create multiple variables with the same name. SAS will not let you do that. Period. You can assign LABELS to variables that will be displayed for many purposes but I find having the same label for multiple variables pretty hard to tell what anything else is.

Also, You say "remove the character "to" ". Is that a typo for "remove the characters "to" " ? Is case important? Such as is "to" treated differently than "To" or "tO" or "TO"?

I suggest actually showing an example data set in the form of data step code to clear up questions as to what you actually have. Then show what you want for that data set.

[Tom]

Makes no sense at all.

Assuming by COLUMN you mean VARIABLE.

Variables cannot be sorted.  Datasets are sorted using the values of one or more BY variables.  Did you mean you want change the order of the variables in the dataset?  Since you are making a NEW dataset by combining two datasets can't you just set the order when you make the new dataset?

You can rename variables.  But the variables in any given dataset have to be uniquely named.  You cannot have two variables that are both named TOP in the same dataset.

I have no idea what you mean by "trimming" a variable.  It makes no sense at all for a numeric variable.  And also no sense for a character variable in SAS.  SAS character variables are fixed length.  If the string being stored is shorter than the length of the variable it is padded with spaces.

What do you mean about removing "to"?  Are you just looking to change any occurance of that two letter string in any character variable?  What do you want to change it into?  To you want to place it with two space characters? one space character?  Nothing?  What if the string to is part of a larger word, like into.  Would you want to convert into to just in?

Show actual input datasets.  The last bit looks like some attempt at psuedo code. Spell out what code you want to actually run (ie using actual SAS syntax).  For example the syntax for the RENAME= dataset option is space delimiter list of pairs of oldname=newname. 

So did you want generate something like

  merge one(rename=(top1=p1  topx=px)) two;