topic Rename a variable the value of another variable in SAS Programming

Rename a variable the value of another variable

SusanLash — Thu, 15 Nov 2018 21:25:18 GMT

I have a data set with baseball players and a lot of info on them:

Name	Team	Var1	Val1	Var2	Val2	Var3	Val3

Allanson, Andy	Cleveland	At_bat	293	n_hits	66	n_home	1
Ashby, Alan	Houston	At_bat	315	n_hits	81	n_home	7
Davis, Alan	Seattle	At_bat	479	n_hits	130	n_home	18
Dawson, Andre	Montreal	At_bat	496	n_hits	141	n_home	20
Galarraga, Andres	Montreal	At_bat	321	n_hits	87	n_home	10

I want to create a program that goes through all of the columns and renames Val_i to the value of Var_i and drops the Var_i columns:

Name	Team	At_bat	n_hits	n_home

Allanson, Andy	Cleveland	293	66	1
Ashby, Alan	Houston	315	81	7
Davis, Alan	Seattle	479	130	18
Dawson, Andre	Montreal	496	141	20
Galarraga, Andres	Montreal	321	87	10

Can anyone help?

Re: Rename a variable the value of another variable

PeterClemmensen — Thu, 15 Nov 2018 21:33:21 GMT

How big is your data set?

Re: Rename a variable the value of another variable

PeterClemmensen — Thu, 15 Nov 2018 21:38:22 GMT

If size is not an issue, then something like this will do

data have;
length name $20 Team $20; 
input Name $ Team $ Var1 $ Val1 Var2 $ Val2 Var3 $ Val3;
infile datalines dlm='|';
datalines;
Allanson, Andy|Cleveland|At_bat|293|n_hits|66|n_home|1
Ashby, Alan|Houston|At_bat|315|n_hits|81|n_home|7
Davis, Alan|Seattle|At_bat|479|n_hits|130|n_home|18
Dawson, Andre|Montreal|At_bat|496|n_hits|141|n_home|20
Galarraga, Andres|Montreal|At_bat|321|n_hits|87|n_home|10
;

data _null_;
   set have;
   if _N_=1 then do;
      call symputx('rename1', Var1);
      call symputx('rename2', Var2);
      call symputx('rename3', Var3);
   end;
   stop;
run;

data want;
   set have(drop=Var:);
   rename Val1=&rename1. Val2=&rename2. Val3=&rename3.;
run;

Re: Rename a variable the value of another variable

Reeza — Thu, 15 Nov 2018 21:42:54 GMT

Do a double transpose. First go to a fully long format and then transpose to a wide format with the IDs.

The last answer here, has an example of how to do that.
https://communities.sas.com/t5/SAS-Studio/Reshaping-data-from-long-to-wide-one-item-s-data-reading-in/m-p/512049/highlight/true#M6558

Re: Rename a variable the value of another variable

SusanLash — Thu, 15 Nov 2018 21:57:04 GMT

Thanks!

This works. It's a pain to put in for each of the 51 variables, but since the names are identical apart from the number, I used excel to write each line.

Is there no way to have a loop that does this? pseudo code: for N = 1 to 51 valN=&renameN

Re: Rename a variable the value of another variable

Reeza — Thu, 15 Nov 2018 21:58:53 GMT

How was the data originally stored? If it was in a form that's like:

VariableName = Value, you can actually handle that in the input stage. Otherwise, you could dynamically create a rename statement, but it's just as good as your Excel since it's a one time operation.

Re: Rename a variable the value of another variable

ballardw — Thu, 15 Nov 2018 21:59:08 GMT

@SusanLash wrote:

I have a data set with baseball players and a lot of info on them:

Name Team Var1 Val1 Var2 Val2 Var3 Val3

Allanson, Andy Cleveland At_bat 293 n_hits 66 n_home 1

Ashby, Alan Houston At_bat 315 n_hits 81 n_home 7

Davis, Alan Seattle At_bat 479 n_hits 130 n_home 18

Dawson, Andre Montreal At_bat 496 n_hits 141 n_home 20

Galarraga, Andres Montreal At_bat 321 n_hits 87 n_home 10

I want to create a program that goes through all of the columns and renames Val_i to the value of Var_i and drops the Var_i columns:

Name Team At_bat n_hits n_home

Allanson, Andy Cleveland 293 66 1

Ashby, Alan Houston 315 81 7

Davis, Alan Seattle 479 130 18

Dawson, Andre Montreal 496 141 20

Galarraga, Andres Montreal 321 87 10

Can anyone help?

If this data started as an external file then I would name the variables when reading them ans keep the correct variables.

Assuming the data started as CSV the code might look something like:

data example;
   infile "sometextfile" dlm=',' dsd;
   informat name $25. team $15. At_bat n_hits n_home best. dropvar $1.;
   input name
         team
         dropvar
         at_bats
         dropvar
         n_hits
         dropvar
         n_home
   ;
   drop dropvar;
run;

If the data was in fixed columns then just read the columns needed and the dropvar wouldn't be needed.

Re: Rename a variable the value of another variable

novinosrin — Thu, 15 Nov 2018 22:00:39 GMT

data have;
length name $20 Team $20; 
input Name $ Team $ Var1 $ Val1 Var2 $ Val2 Var3 $ Val3;
infile datalines dlm='|';
datalines;
Allanson, Andy|Cleveland|At_bat|293|n_hits|66|n_home|1
Ashby, Alan|Houston|At_bat|315|n_hits|81|n_home|7
Davis, Alan|Seattle|At_bat|479|n_hits|130|n_home|18
Dawson, Andre|Montreal|At_bat|496|n_hits|141|n_home|20
Galarraga, Andres|Montreal|At_bat|321|n_hits|87|n_home|10
;

data _null_;
set have(obs=1);
call execute('data have;set have;rename');
array vx(*) var:;
array vy(*) val:;
do i=1 to dim(vx);
call execute (vname(vy(i))||'='||vx(i)||' ');
end;
call execute(';drop var:;run;');
run;

Re: Rename a variable the value of another variable

SusanLash — Thu, 15 Nov 2018 22:44:44 GMT

Works perfectly and it's easy! Thank you.

Re: Rename a variable the value of another variable

novinosrin — Thu, 15 Nov 2018 22:46:40 GMT

Most welcome!