topic Re: rename variables in SAS Programming

rename variables

sateh — Thu, 24 Nov 2022 01:23:55 GMT

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.

Re: rename variables

Tom — Thu, 24 Nov 2022 01:48:11 GMT

What are you having trouble with?

Is it getting the list of variable names?

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

Is it generating the new name?

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?

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

Or applying the renames?

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

Re: rename variables

Reeza — Thu, 24 Nov 2022 02:58:37 GMT

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;

Re: rename variables

sateh — Thu, 24 Nov 2022 14:42:10 GMT

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

Re: rename variables

PaigeMiller — Thu, 24 Nov 2022 14:49:16 GMT

@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.

Re: rename variables

sateh — Thu, 24 Nov 2022 15:05:59 GMT

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

Re: rename variables

PaigeMiller — Thu, 24 Nov 2022 15:10:26 GMT

Make this change

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

Re: rename variables

sateh — Thu, 24 Nov 2022 15:15:36 GMT

Thank you very much this really worked for me

Re: rename variables

Tom — Thu, 24 Nov 2022 15:36:43 GMT

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.

Re: rename variables

sateh — Fri, 25 Nov 2022 14:25:21 GMT

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
.
.
.
.

Re: rename variables

Reeza — Fri, 25 Nov 2022 16:32:58 GMT

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)