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!
Well, you can do this dynamically which is what I think you're after.
A portion of that - the latter part at least is demo'd here:
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!
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.
Well, you can do this dynamically which is what I think you're after.
A portion of that - the latter part at least is demo'd here:
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
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.
@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.
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.
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.
The error is in your code...you have to post your code as well, not just the errors.
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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: