topic Re: Renaming variable with a substr in SAS Programming

Renaming variable with a substr

Onizuka — Mon, 27 May 2019 08:27:24 GMT

Hello,

(sorry for my english)

I want rename some variables and delete the last 2 characters (or one character, look at below) :

for example, I have a variable : namevar_1_1 and an other namevar_1_10.

What I want to do is rename these variables to delete the caracters after the last underscore :

namevar_1_1 becomes namevar_1_

namevar_1_10 becomes namevar_1_

How can we do this ?

I dont have any idea.. I tryed by going through a rename using SUBSTR but doesn't work..

Here a data set example :

Data have ;
format namevar_1_1 1. ;
input namevar_1_1 ;
cards;
1
;

Data have ; 
format namevar_1_10 1. ; 
input namevar_1_10 ; 
cards; 
1 
;

Thank's in advance !

Onizuka

Re: Renaming variable with a substr

PeterClemmensen — Mon, 27 May 2019 08:23:02 GMT

Two variables can not have the same name. You are asking to name both the variables namevar_1_?

Re: Renaming variable with a substr

Onizuka — Mon, 27 May 2019 08:34:03 GMT

The variables are in two different tables sorry i'm changing on the topic but they have to have the same name because i'm appending them after !

For understand what i'm doing this is an example :

Data have ;
format namevar_1_1 1. namevar_1_2 1. namevar_1_3 1.;
input namevar_1_1 namevar_1_2 namevar_1_3;
cards;
1 3 2
2 . .
3 1 .
;

Each line is a response make by a respondent (for a inverstigation).

There is 3 variables but it the same question :

line 1 : The respondent put modality 1 at FIRST CHOICE

Modality 3 on SECOND CHOICE and modality 2 at last choice (he made 3 choices on this order 1 3 2).

line 2 : An other respondent just put the modality 2 at first choice and he doesn't put another choice

Re: Renaming variable with a substr

PeterClemmensen — Mon, 27 May 2019 08:39:54 GMT

But here, you still have all the variables in the same data set?

Please be more specific about your requirements 🙂 Show us a representation of what you have and what you want

Re: Renaming variable with a substr

Onizuka — Mon, 27 May 2019 08:43:39 GMT

Yes sorry I'm not explaining very well ..

I'm treating the results question by question so my REAL QUESTION is :

How can we rename a variable for keep what is before the last "_" :

namevar_1_1 : namevar_1_

Let say that I have only one table with only this variable 😛

Re: Renaming variable with a substr

Onizuka — Mon, 27 May 2019 09:41:05 GMT

It work doing like this :

Data have ;
format namevar_1_1 1. ;
input namevar_1_1  ;
cards;
1
;

Proc contents data = have out = c_have (keep = name) noprint ; run ;

Data want ;
set c_have ;
name1 = COMPRESS(SCAN(name, 1, '_')!!"_"!!SCAN(name,2,'_')!!"_");
run;

Proc sql noprint ; select name1 into: name1 from want ; quit ;

data have2 ;
set have ;
rename namevar_1_1 = &name1. ;
run ;

Re: Renaming variable with a substr

Astounding — Mon, 27 May 2019 14:59:19 GMT

You are moving in the right direction. However, it's a little more complicated than what you have done:

You assume you already know the name of the original variable in the RENAME statement.
You may be assuming that all variable names contain at least two underscores.

Here is a more general version of what you are attempting:

proc contents data=have out=c_have (keep=name) noprint;
run;

file renames temp;

data _null_;
set c_have;
if countw(name, '_') > 2;
len_newname = length(name) - length(scan(name, -1, '_'));
newname = substr(name, 1, len_newname);
file renames noprint;
put 'rename ' name '=' newname ';' ;
run;

data want;
set have;
%include renames;
run;

This is untested code, so you will need to try it out.

Also, it assumes that you won't run into name conflicts, such as original variable names that include both var_1_1 and var_1_2 both being renamed to var_1.

Re: Renaming variable with a substr

Onizuka — Wed, 29 May 2019 09:25:43 GMT

@Astounding wrote:
You are moving in the right direction. However, it's a little more complicated than what you have done:

You assume you already know the name of the original variable in the RENAME statement.
You may be assuming that all variable names contain at least two underscores.
Also, it assumes that you won't run into name conflicts, such as original variable names that include both var_1_1 and var_1_2 both being renamed to var_1.

Hello @Astounding ,

Thank you for your answer !

The first step works well and it put this on the log :

NOTE: The file RENAMES is:
      Nom du fichier=C:\Users\XXXX\renames.dat, /*renames.dat */
      RECFM=V,LRECL=32767,
      Taille de fichier (octets)=0,
      Modifié(e) le=29 mai 2019 11 h 21,
      Heure de création=29 mai 2019 11 h 16

but it doesn't work on the last step :

data want;
set have;
%include renames;
run;

Here the log error :

71   data want;
72   set have;
73   %include renames;
WARNING: Physical file does not exist, C:\Users\XXXX\renames.sas. /*renames.sas*/
ERROR: Cannot open %INCLUDE file RENAMES.
74   run;

Re: Renaming variable with a substr

Tom — Wed, 29 May 2019 13:38:13 GMT

Looks like you skipped the line that defined RENAMES as a fileref.

filename renames temp;

So the data step step took as 'renames' as the base name for an output data file and add '.dat' extension. And the %include figured that renames was the base name for the SAS program file and added '.sas' extension and so did not find the file the data step wrote.

Re: Renaming variable with a substr

Astounding — Wed, 29 May 2019 13:41:46 GMT

The surest, most flexible solution is to replace this statement:

filename renames temp;

Instead, pick a location where you would like to store all the renaming code. Select an existing folder, and a filename within that folder. For example:

filename renames 'path_to_folder\renm.sas';

Just supply the name of the folder and file that you select within the quotes. This also lets you examine the file later, to better understand how the program is working.

Re: Renaming variable with a substr

Onizuka — Wed, 29 May 2019 14:38:51 GMT

@Astounding wrote:
The surest, most flexible solution is to replace this statement:
filename renames temp;
Instead, pick a location where you would like to store all the renaming code. Select an existing folder, and a filename within that folder. For example:
filename renames 'path_to_folder\renm.sas';
Just supply the name of the folder and file that you select within the quotes. This also lets you examine the file later, to better understand how the program is working.

Thank you for the answer, i will try your solution using this, thank you very much 🙂