Adding (') to the left of string and ('n) to the right of string. E.g. - change India to 'India'n

[SaketChaudhari]

Hi everyone!

I am currently trying to change the column names in my sas dataset from _yyyy_mm to yyyy_mm. (e.g.- _2021_07 to 2021_07)

For doing that, I implemented the dictionary.columns method, from where I copied the column names relevant to me into a table, and then named them as col_old

After doing that I made a mapping table where I used a simple substr() function on col_old to get col_new in the form relevant to me. (used substr() to extract yyyy_mm from _yyyy_mm)

But when I tried to insert all this into a variable called rename_list and use proc datasets with &rename_list. the log showed error, as the variable would start with a number (like in 07_2021). The log would say expecting a character or a name.

So, I included options validvarname = any; at the top of the code.

But now, it changed the column names to a different format already. It changed it to mm-yyyy (07-2021). Also, when I found the column description, it showed the name of the column as '07-2021'n

So long story short, I want your help to use some functions to change the mm-yyyy to the form of 'mm-yyyy'n for the col_old, and then to the form of 'yyyy_mm'n for col_new

So that I can use these two to create my dynamic variable list_rename and mention it in proc datasets to rename dynamically.

Thanks and Kind Regards

[Reeza]

NLITERAL() function.

select nliteral(name) as name from ....

---

@SaketChaudhari wrote:

Hi everyone!

I am currently trying to change the column names in my sas dataset from _yyyy_mm to yyyy_mm. (e.g.- _2021_07 to 2021_07)

For doing that, I implemented the dictionary.columns method, from where I copied the column names relevant to me into a table, and then named them as col_old

After doing that I made a mapping table where I used a simple substr() function on col_old to get col_new in the form relevant to me. (used substr() to extract yyyy_mm from _yyyy_mm)

But when I tried to insert all this into a variable called rename_list and use proc datasets with &rename_list. the log showed error, as the variable would start with a number (like in 07_2021). The log would say expecting a character or a name.

So, I included options validvarname = any; at the top of the code.

But now, it changed the column names to a different format already. It changed it to mm-yyyy (07-2021). Also, when I found the column description, it showed the name of the column as '07-2021'n

So long story short, I want your help to use some functions to change the mm-yyyy to the form of 'mm-yyyy'n for the col_old, and then to the form of 'yyyy_mm'n for col_new

So that I can use these two to create my dynamic variable list_rename and mention it in proc datasets to rename dynamically.

Thanks and Kind Regards

---

[Reeza]

Here's a neat way to change variable names using a data step. 

*fake data to play with;
data class;
set sashelp.class;
run;

*data set name to fix;
*making code slightly easier to follow;
%let dsn_in = CLASS;

data _null_;
set sashelp.vcolumn end=eof;
where libname = 'WORK' and memname = "&dsn_in";

 if _n_=1 then
    call execute ("proc datasets lib=WORK nodetails nolist; modify &dsn_in; rename ");
 
 *create new variable name here;
 *in this example just renaming it to VAR001 - VAR###;
 *variable name is 'name';
 new_name=catt('VAR', put(_n_, z3.));
 
 *pass new and old name to proc datasets;
 call execute (name);
 call execute ('=');
 call execute (new_name);
 
 *if last record then quit;
 If eof then
    call execute (';run;quit;');
    
run;

[SaketChaudhari]

Thank you for this suggestion.

As far as I understand, this is going to be very static. Every month, someone will have to go deep in the code to find these lines, and then change the months by typing. I was hoping something more dynamic, hence, I went for dictionary.columns method. 

Basically, every month, the first month (fourth column) and the last month (last colummn) will increase by one, so as  to have 24 months in analysis period.

Kindly let me know if you understand this reply, and if there is something else you could suggest me.

[Reeza]

This uses sashelp.vcolumn which is the same as dictionary.columns so it's fully dynamic.

Where would you change something in the code? You haven't provided enough of a use case to comment on the bet methods..

If you have month names as column headers you're storing your data in an inefficient structure. It should typically be in a long form and then transposed for reporting at the end which is then fully dynamic and adapts to new months. Or as the last step in the process so this is a non-issue.

[SaketChaudhari]

Yup sorry for not explaining properly. This was my first question ever. I'll try to explain properly as I ask more questions.

Basically I get the raw data from somewhere, in the horizontal format (months become column headers, plus, few extra headers that need not be changed). This happens because raw data set will become too large if the months are not horizontally present (i.e., when the table is vertical). I do my analyses into this raw data, and export it. I want to improve this export process, where I will be able rename months as suggested by me in the original question.

The months have integer values in them, so maybe I will be able to boil the column details from sashelp.vcolumn using library, memtype and datatype (kindly tell me if I am wrong with this assumption)

And then do the necessary changes suggested by you.
Earlier, I thought I will have to manipulate a variable (new_name) by using some string functions like substr() and catx(), to extract new name out of a variable called old_name.



I'll try your method and will let you know.

Thanks and kind regards

[SaketChaudhari · Accepted solution]

So I figured it out. Sorry I was not keeping track of my log, so I don't have screenshots of the error as they popped in, but I have explained what error I had faced in the comments. I will directly paste the code here, to explain everything that I could not explain properly in my question, my most humble apologies for that. Somehow, I forgot to tell you that the first three variables are character, which I guess drastically affects the earlier solution that you gave.

A big thanks to your sashelp.vcolumns suggestion, after which I could solve this. Request you to not micro-analyse my side of this, as all this is from someone who is fairly a fresher.

data pg1.saket;
input from$ to$ Geo$ _2020_01 _2020_02 _2020_03 _2020_04 _2020_05;
datalines;
a b mah 3 4 5 6 7
c d ori 4 5 6 1 2
e f nep 5 4 3 1 66
g h kin 3 6 4 55 7
i j yup 44 5 3 22 55 
;
run;

data vcolumns;
set sashelp.vcolumn;
where libname = "PG1" and memname = "&data_change." and type="num";
run;

/* duplicating this table from sashelp.vcolumn because it was not working somehow in proc sql alone*/

%let data_change = SAKET;

options validvarname = any; 
/* because otherwise log was flagging an error, saying that it is expecting a name, 
since 2020_07,and other such months begin by a number, 
which is not allowed in sas variable naming convention*/

proc sql;
create table pg1.col_name as
select name as col_name
from work.vcolumns
quit;

data new_name;
set pg1.col_name;

new = substr(catx("%bquote('n)",catx ("%bquote(')","@",substr(col_name,2,7)),"|"),2,10);
run;

/* It took me a proper hour to get this. There was a problem right from putting
 quotes inside double quotes, which was creating problem, so I had to search 
and use %bquote and all that stunt. */
/* Basically, in the previous step, i converted _yyyy_mm in to the format of 'yyyy_mm'n */

proc print data= new_name;
run;

PROC SQL;
	SELECT CATX("=", COL_NAME, new)
		INTO :LIST_RENAME SEPARATED BY " "
		FROM WORK.new_name;
QUIT;
 
%PUT &LIST_RENAME.;

PROC DATASETS LIB=pg1;
	MODIFY saket;
	RENAME &LIST_RENAME.;
RUN;

proc print data=pg1.saket;
run;

Attaching images of result for your reference:

Thank you for helping me out with this. Kindly urge you to copy this or something so that I could mark it as your answer, so that you could get your due credit. I feel you would have definitely explained this to me, perhaps more fluently and smoothly, had I explained the question a tad bit better in the first place.

Thanks and Kind Regards

❤️