topic Re: Rename strings in all variables with given IDs in SAS Programming

Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 16:06:07 GMT

Hi,

I have created the following IDs based on repeated 'states' in my input data.

states	ID
ABC_MNO_S3	S1
ABC_S1	S2
ABC_S2	S3
ABC_PQR_S3	S4
XYZ_MNO_S3	S5
XYZ_S1	S6
XYZ_S2	S7
XYZ_PQR_S3	S8

Is there an easy way to rename all the variable (column) names of my current data with the IDs?

Basically, whenever 'XYZ_PQR_S3' appears, I want it to be 'S8'. If there's a column name 'FromXYZ_PQR_S3_to_A' I want it to be renamed to 'FromS8_to_A'. This is true for all 8 (could be more) 'state'here.

Thanks!

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 16:23:12 GMT

I think you need to provide more details of what you're trying to achieve here. It's not clear to me if you're trying to change variable names or values.

@ss59 wrote:

Hi,

I have created the following IDs based on repeated 'states' in my input data.

states ID

ABC_MNO_S3 S1

ABC_S1 S2

ABC_S2 S3

ABC_PQR_S3 S4

XYZ_MNO_S3 S5

XYZ_S1 S6

XYZ_S2 S7

XYZ_PQR_S3 S8

Is there an easy way to rename all the variable (column) names of my current data with the IDs?

Basically, whenever 'XYZ_PQR_S3' appears, I want it to be 'S8'. If there's a column name 'FromXYZ_PQR_S3_to_A' I want it to be renamed to 'FromS8_to_A'. This is true for all 8 (could be more) 'state'here.

Thanks!

Re: Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 16:26:56 GMT

Hi @Reeza, I'm trying to change variable names. So, most of the variables in my data contains one of these eight strings, and I want to replace them with ith IDs in the ID column without changing the actual data.

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 16:31:00 GMT

Well, you can do this dynamically which is what I think you're after.

Use SASHELP.VCOLUMN to get the list of names in your table (or PROC CONTENTS).
In a data step:
1. Load the names you're looking up into a temporary array
2. Search through each name and use TRANWRD to replace the text you want, making sure to store it in a new variable.
3. This will create a data set with the old names and the new names you want.
Use PROC SQL to build your RENAME statement (this can be done in previous data step but I find it easier to separate it out at first.
Use PROC DATASETS with the RENAME statement to rename the variables.

A portion of that - the latter part at least is demo'd here:

https://gist.github.com/statgeek/82d9f2854edc01560e0f

Re: Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 17:32:02 GMT

I am being able to get the new column with new names, but proc datasets with rename statement giving me errors. Also after using 'tranwrd' the order of the variables are changing and it is difficult to sort if that happens

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 17:35:17 GMT

Post your code.

If the order is changing you likely did something incorrect in the code, the order shouldn't change in a data step.

Re: Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 18:03:27 GMT

@Reeza here it is:

proc contents data =  parameters out= parameter_names (Keep= Name) order=varnum;
run;

/*make it dynamic later*/

data new_names;
set parameter_names;
Name = tranwrd(Name, "NFBE_NUTP_S3", "S5");
Name = tranwrd(Name, "NFBE_S1", "S6");
Name = tranwrd(Name, "NFBE_S2", "S7");
Name = tranwrd(Name, "NFBE_UTP_S3", "S8");
Name = tranwrd(Name, "FBE_NUTP_S3", "S1");
Name = tranwrd(Name, "FBE_S1", "S2");
Name = tranwrd(Name, "FBE_S2", "S3");
Name = tranwrd(Name, "FBE_UTP_S3", "S4");
put Name;
run;

/*create the list*/
proc sql;
select Name 
into :rename_list
separated by " "
from new_names;
quit;


%put &rename_list;

proc datasets library=work nolist;
modify parameters;
rename &rename_list;
run; quit;

'tranwrd' is changing the order of the 'names', because it is affecting selective rows.

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 18:07:15 GMT

Step 2.2 -> make sure to save it to a new variable....

To rename you need to have two variables, one is the new name and one is the old name, did you run/try the example I linked to? You can see how the macro variable looks and what yours should look like.

Search through each name and use TRANWRD to replace the text you want, making sure to store it in a new variable.

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 18:08:52 GMT

And you can keep varnum to sort it if desired - you don't keep it at the moment to maintain order, though I still don't see how it could possibly reorder the data.

Re: Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 18:28:25 GMT

sorry, I created the correct list of things to rename, but now I'm getting following errors in the proc datasets:

ERROR: Variable Year already exists on file WORK.PARAMETERS.
ERROR: Variable Period already exists on file WORK.PARAMETERS.
ERROR: Variable Segment1 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment2 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment3 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment4 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment5 already exists on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_S2 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_S1 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_S2 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_S1 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_UTP_S3 is not on file WORK.PARAMETERS.

I am supposed to modify the names, so if variable already exists, that should not be a problem. And the other variable do exist in the data.

Re: Rename strings in all variables with given IDs

Reeza — Tue, 19 Dec 2017 18:36:34 GMT

The error is in your code...you have to post your code as well, not just the errors.

Re: Rename strings in all variables with given IDs

ss59 — Tue, 19 Dec 2017 18:47:07 GMT

Never mind, it was done correctly before, which I didn't check and then in the second run it didn't find many of the variables since they were already renamed.

Thanks for your help!