Simple way rename the multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Simple way rename the multiple variables

[ Edited ]

Hello to whoever can help

 

please see the following screenshots

Capture.PNG

Capture.PNGCapture1.PNG

Capture1.PNG

is there an easy way to rename those returnv1 to return13 with new variable names such as CVS_return, VZ_return, PEP_return etc.?

is there macro for it?

 

thank you!


Accepted Solutions
Solution
‎01-24-2016 06:07 PM
Super Contributor
Posts: 490

Re: Simple way rename the multiple variables

[ Edited ]

Or do not rename ... prepare your variables names first

 

proc sql noprint;                                                                                                                       
  select trim(name)||'_return'                                                                                             
  into :varlist separated by ' '                                                                                                        
  from DICTIONARY.COLUMNS                                                                                                               
  WHERE LIBNAME EQ "QUANDL" and MEMNAME EQ "PORT"                                                                                            
  and name not in ('Date')
  order by  varnum;     /* Column Number in Table*/                                                                                                             
quit;


data Portfolio ;
set quandl.port;
retain &varlist;
array Variable{13} CVS -- DIS; 
array ReturnV {13} CVS_return -- DIS_return;
	do i= 1 to 13;
 		ReturnV[i]=log(lag(Variable[i]) / Variable[i]);
	end;
run;

View solution in original post


All Replies
Super User
Posts: 17,963

Re: Simple way rename the multiple variables

Don't automatically jump to a macro for the solution. 

You can use the SASHELP.VCOLUMN table to help you dynamically generate the variable names, and the variable list for the rename statement. In your case the variable order is important, so make sure to look at the table to determine which variable that is and how you'll need to filter the table.

 

Here's an example that should help you get started - you need to change the SQL code, the WHERE criteria as well as the last portion of the CATX function that generates the new name.   The CAT portion is below, the WHERE condition you can try on your own.

 

catt('DT_', tranwrd(upper(name), '_DATE', ' '))

to

 

catt(name, "_return")

https://gist.github.com/statgeek/82d9f2854edc01560e0f

Super User
Posts: 17,963

Re: Simple way rename the multiple variables

PS..please post your code as text, not as pictures, we can't copy and paste pictures.

 

Same goes for data. If we have to generate fake data or even type out your data from a picture you're less likely to get an answer that includes code.  Make it easier for others to help you Smiley Wink

Solution
‎01-24-2016 06:07 PM
Super Contributor
Posts: 490

Re: Simple way rename the multiple variables

[ Edited ]

Or do not rename ... prepare your variables names first

 

proc sql noprint;                                                                                                                       
  select trim(name)||'_return'                                                                                             
  into :varlist separated by ' '                                                                                                        
  from DICTIONARY.COLUMNS                                                                                                               
  WHERE LIBNAME EQ "QUANDL" and MEMNAME EQ "PORT"                                                                                            
  and name not in ('Date')
  order by  varnum;     /* Column Number in Table*/                                                                                                             
quit;


data Portfolio ;
set quandl.port;
retain &varlist;
array Variable{13} CVS -- DIS; 
array ReturnV {13} CVS_return -- DIS_return;
	do i= 1 to 13;
 		ReturnV[i]=log(lag(Variable[i]) / Variable[i]);
	end;
run;
Super User
Posts: 17,963

Re: Simple way rename the multiple variables

PROC SQL doesn't guarantee order.  You should consider adding an ORDER clause otherwise you might get some variables out of order.

 

 

Contributor
Posts: 31

Re: Simple way rename the multiple variables

Thank you Reeza!

 

I will keep that in mind.

Super Contributor
Posts: 490

Re: Simple way rename the multiple variables

Thanks @Reeza i did 

Contributor
Posts: 31

Re: Simple way rename the multiple variables

[ Edited ]

Thank you @mohamed_zaki

 

Where did you learn all of these if you dont mind I am asking?  Is there User Guide for it? And in which SAS User Guide does it talk about retain and array?

 

In that PROC SQL program I understand everything except the part, seperated by ' ', why do we need to seperate it?

 

Thanks!

Super User
Posts: 10,550

Re: Simple way rename the multiple variables

[ Edited ]

Retain and Array are in the base SAS language reference documentation.

 

The INTO : structure creates a SAS Macro variable. If you specify a single variable without separated you get a single value. The separated puts all of the values into a single variable but you need to specify something to tell the different values apart.

How useful would string like this be

AgeHeightNameSexWeight

when you want to do something with each of

Age Height Name Sex Weight

 

Separated by also lets you designate different characters for different uses as some places want comma separated names (SQL) or you may want to process something in a different manner and use something like a pipe to tell whan values end such as:

 

Smith, John|Mary Jones|Some really long name with Jr.

where the , space or . are part of the value you a going to manipulate.

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 594 views
  • 4 likes
  • 4 in conversation