topic Convert all column labels to column name in SAS Programming

Convert all column labels to column name

tfarkas — Wed, 27 Mar 2019 18:43:12 GMT

I am using PROC SQL to develop tables for use in SAS Visual Analytics (v7.4). However, I spend time modifying column names in SQL with the AS keyword, but this changes only the name, and not the label that gets added by default, and SAS VA displays column labels, not names, and as far as I can tell, there is no option for VA to display names, not labels.

Can somebody provide a programmatic solution to converting all of the column labels in a table to the name for that column? Is there an option in PROC SQL, or perhaps a statement in the DATA step that might solve this problem? Or should I go about writing a macro to loop through the column names?

I tried this, and it doesn't work, providing an error: "Expecting an =."

proc sql;

select 	name
into 	:colnames separated by ' '
from 	dictionary.columns
where 	libname='SASHELP' 
	and	memname='CARS'
;
quit;

data cars_w_labs;
set SASHELP.CARS;

do i=1 to countw("&colnames");

label scan("&colnames", i) = scan("&colnames", i);

end;

run;

Re: Convert all column labels to column name

Kurt_Bremser — Wed, 27 Mar 2019 18:57:15 GMT

data cars;
set sashelp.cars;
run;

data _null_;
set sashelp.vtable (where=(libname='WORK' and memname = 'CARS')) end=eof;
if _n_ = 1 then call execute("
  proc datasets library=work;
  modify cars;
  label 
");
call execute(strip(name) !! '="' !! strip(name) !! '" ');
if eof then call execute('; quit;');
run;

Re: Convert all column labels to column name

ballardw — Wed, 27 Mar 2019 19:29:13 GMT

You can assign attributes such as label and format to variables when created in Proc SQL

data have;
   x=23;
run;

proc sql;
   select (2* x) as xx label='Some label' format=z4.
   from have
   ;
quit;

or suppress the label

data have;
   x=23;
   label x='Some longer label'
run;

proc sql;
   select  x  label='' 
   from have
   ;
quit;

Re: Convert all column labels to column name

Reeza — Wed, 27 Mar 2019 19:36:10 GMT

Pretty sure if you remove all labels it defaults to column names instead. You can remove all labels from your variables using the following code. Note that this does not regenerate your data sets, it modifies the metadata in place, so will be quite fast, as will other PROC DATASETS solutions.

proc datasets library=mylib nolist;
  modify mydataset;
  attrib _all_ label='';
quit;

@tfarkas wrote:

I am using PROC SQL to develop tables for use in SAS Visual Analytics (v7.4). However, I spend time modifying column names in SQL with the AS keyword, but this changes only the name, and not the label that gets added by default, and SAS VA displays column labels, not names, and as far as I can tell, there is no option for VA to display names, not labels.

Can somebody provide a programmatic solution to converting all of the column labels in a table to the name for that column? Is there an option in PROC SQL, or perhaps a statement in the DATA step that might solve this problem? Or should I go about writing a macro to loop through the column names?

I tried this, and it doesn't work, providing an error: "Expecting an =."
proc sql;

select 	name
into 	:colnames separated by ' '
from 	dictionary.columns
where 	libname='SASHELP' 
	and	memname='CARS'
;
quit;

data cars_w_labs;
set SASHELP.CARS;

do i=1 to countw("&colnames");

label scan("&colnames", i) = scan("&colnames", i);

end;

run;

Re: Convert all column labels to column name

Ksharp — Thu, 28 Mar 2019 12:38:00 GMT

Try option :

options nolabel ;

Or try Reeza 's proc datasets code .