DATA Step, Macro, Functions and more

Add Suffix to all variables in dataset

Reply
Frequent Contributor
Posts: 78

Add Suffix to all variables in dataset

I'm looking for a fast way to do this. I need to add the following to the end of all variables in the dataset:

"_3mo"

so that

ID = ID_3mo

Thanks!
Super Contributor
Posts: 359

Re: Add Suffix to all variables in dataset

This will work.

proc sql;
select cat(name, ' = ', cats(name, '_3mo' )) into :renstr separated by ' ' from
dictionary.columns where libname = 'WORK' and memname='FMIN';
quit;

data new;
set fmin (rename = (&renstr));
run;
Frequent Contributor
Posts: 78

Re: Add Suffix to all variables in dataset

There are three of us here and we can't get this to work. We constantly get the message that there are no rows, which means no observations are brought in.

I think we must be missing something... Message was edited by: statadm
Super Contributor
Super Contributor
Posts: 3,174

Re: Add Suffix to all variables in dataset

Suggest you relax the WHERE clause. The code presumes that you have a SAS file identified in the WHERE clause by the LIBNAME and the MEMNAME variables which must either have been created in this SAS session or referenced with a prior LIBNAME command.

Share what code you are attempting to use and do so in a POST reply as a SAS log.

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 359

Re: Add Suffix to all variables in dataset

Make sure you run the SQL after you have created the data set with the variables you want to rename.
Frequent Contributor
Posts: 78

Re: Add Suffix to all variables in dataset

Thanks to everyone, it is working now.

I didn't realize that the libname and memname had to be UPCASE. I'm obviously new to proc sql.

Thanks again!
Valued Guide
Posts: 2,177

Re: Add Suffix to all variables in dataset

statadm
it's not so much about sql as about the data it was using.
Have a look at the underlying data. Search on the support.sas.com website (most pages have a search box) for dictionary.columns, in product documentation. It will refer you to page titled "Programming with the SQL Procedure: Accessing SAS System Information by Using DICTIONARY Tables" at http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001385596.htm describing many of these dictionary tables, and pointing out that they are also available as "sashelp views". Although you cannot proc print the dictionary.something tables, you can refer to their sashelp.v{name} in proc contents and proc print.
So, to print the first few lines of dictionary.tables, [pre]proc print data= sashelp.vtable(obs=10);
run;[/pre] Or, when running SAS directly
(with Enterprise Guide drill through the server list to libraries, SASHELP and pick VCOLUMN)
[without Enterprise Guide, you can drill through the SAS Explorer into SASHELP library and open the item "Vcolumn"]
It might take longer to load than that proc print of the first 10 lines, but it is worth the time to experience and discover the extensive nature of the information these sashelp.v{name} aka dictionary tables, offer

PeterC
Frequent Contributor
Posts: 78

Re: Add Suffix to all variables in dataset

Thanks Peter for helping me to understand.
Respected Advisor
Posts: 3,799

Re: Add Suffix to all variables in dataset

No need to read any records just use &renstr in PROC DATASETS

[pre]
proc datasets library=...;
modify ...;
rename &renstr;
run;
[/pre]
Ask a Question
Discussion stats
  • 8 replies
  • 7540 views
  • 1 like
  • 5 in conversation