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;
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;
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;
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;
Try option :
options nolabel ;
Or try Reeza 's proc datasets code .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.