rename variables

[sateh]

I have a table that has many columns and I need to remove the word P_ld from those columns and the word that follows after the last _ with the number, I have tried to do it but it has not been possible, attached image of my table.

The idea is to be able to remove what I have highlighted in yellow for all the variables, there are many variables.

[Tom]

What are you having trouble with?

Is it getting the list of variable names?

Spoiler

proc contents data=HAVE noprint out=contents; run;

Is it generating the new name?

Spoiler

data contents;
  set contents ;
  if upcase(name)=:'P_' and substrn(name,length(name)-2)='842');
  newname = substr(name,3,length(name)-5);
run;

Generating the pairs?

Spoiler

proc sql noprint;
select catx('=',name,newname)
  into :renames separated by ' '
  from contents
;
quit;

Or applying the renames?

Spoiler

data want;
  set have;
  rename &renames;
run;

[Reeza]

A slightly different approach than Tom's that uses the sashelp.vcolumn table and macro variables. 

data class;
	set sashelp.class;
	rename weight=p_random1_3343 height=p_random2_343 age=p_random3_34343;
run;

*create renaming list;

proc sql noprint;
	select catx (' = ', name, scan(name, 2, "_")) into :rename_list separated by 
		" " from sashelp.vcolumn where libname='WORK' and memname='CLASS' and 
		upcase(name) like 'P_%';
quit;

*rename variables;

proc datasets lib=work nodetails nolist;
	modify class;
	rename &rename_list;
quit;

*check that rename worked;

proc contents data=class;
run;

[sateh]

This approach has been useful for me, however, I still can't remove a certain part of the column name, if you look at the image, the column names have a prefix called P_ld and then the name follows, so with that approach that you shared with me, I can remove the P_ and _ with numbers, but the idea is that it should look like this, for example:

P_ldACCESOALAADMINDEJUSTIC_842 = ACCESOALAADMINDEJUSTIC

[PaigeMiller]

---

@sateh wrote:
This approach has been useful for me, however, I still can't remove a certain part of the column name

---

Please give details when something doesn't work. Please show us the code you used. If there are errors in the LOG, also show us the log. If the end result is not what you expect, show us the end result.

IMPORTANT: when you say something doesn't work, and you give no other details about what you did, we really can't help.

[sateh]

this is my input table

The idea is to rename the columns so that the columns remain without the word P_ld and without _ with the number

Here I provide the code of how I am doing it as indicated @Reeza 

proc sql noprint;
	select catx (' = ', name, scan(name, 2, "_")) into :rename_list separated by 
		" " from sashelp.vcolumn where libname='WORK' and memname='RESULTADOS_VAR_CLEAN' and 
		upcase(name) like 'P_%';
quit;


*rename variables;

proc datasets lib=work nodetails nolist;
	modify resultados_var_clean;
	rename &rename_list;
quit;

and it works, this is the result, but if you look at the name of the variables I need to also remove what is highlighted in yellow, that is, the word Id

[PaigeMiller · Accepted solution]

Make this change

select catx (' = ', name, substr(scan(name, 2, "_"),3))

[sateh]

Thank you very much this really worked for me

[Tom]

Reminder: Posting photographs of text (in this case variable names) makes it hard for someone to help you.  Please post data as text so that it can be used to help you.

[sateh]

I am presenting the following problem, it is bringing me only a part of the columns

This is how the columns of my table are, I need to eliminate what is highlighted in yellow, that is, P_ld and _with the number

I am using this code suggested by @Reeza but it is giving me this result.

proc sql noprint;
	select catx (' = ', name, substr(scan(name, 2, "_"),3)) into :rename_list separated by 
		" " from sashelp.vcolumn where libname='WORK' and memname='RESULTADOS_VAR_CLEAN' and 
		upcase(name) like 'P_%';
quit;

but what I really need is for the entire word to remain example:

ACCESO_ADMIN_JUSTICIA
AYUDA_HUMANITARIA
DEBIDO_PROCESO
DERECHO_DE_PETICION
.
.
.
.

[Reeza]

data class;
	set sashelp.class;
	rename weight=p_random1_3343 height=p_random2_343 age=p_random3_34343;
run;

*create renaming list;

proc sql noprint;
	select catx (' = ', name, substr(name,3,length(name)-5)) into :rename_list separated by 
		" " from sashelp.vcolumn where libname='WORK' and memname='CLASS' and 
		upcase(name) like 'P_%';
quit;

*rename variables;

proc datasets lib=work nodetails nolist;
	modify class;
	rename &rename_list;
quit;

*check that rename worked;

proc contents data=class;
run;

The SCAN won't work because you have multiple _ in the variable name, unlike the picture you posted. 

Use this instead for the second part of the name, see code above.

substr(name,3,length(name)-5)