I'm trying to export a file to CSV and include two line headers, one with labels and one with names. Unfortunately the names contain special characters such as commas. I'm assuming I need to mask it somehow or use single quotes or something, but nothing I've tried so far works.
I keep getting an error on the data step code. See the code and error message below.
Any help appreciated.
*Create demo data;
data class;
set sashelp.class;
label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;
*select names into a macro variable name_list;
*Select labels into a macro variable label_list;
proc sql noprint;
select trim(name) into :name_list separated by ", "
from sashelp.vcolumn
where libname='WORK' and memname='CLASS' and not missing (name);
select quote(trim(coalesce(label, name))) into :label_list separated by ", "
from sashelp.vcolumn
where libname='WORK' and memname='CLASS';
quit;
*check macro variables;
%put Name= &name_list;
%put;
%put Label= &label_list;
filename fout 'C:\_localdata\temp\Class.csv';
data _null_;
set class;
file fout dlm=',';
if _n_=1 then
do;
put "&label_list";
put "&name_list";
end;
put (_all_)(:);
run;
Log:
1730 filename fout 'C:\_localdata\temp\Class.csv';
1731
1732 data _null_;
1733 set class;
1734 file fout dlm=',';
1735
1736 if _n_=1 then
1737 do;
1738 put "&label_list";
NOTE: Line generated by the macro variable "LABEL_LIST".
1 ""Name", "Sex", "Age, Years", "Height, inches", "Weight(lbs)"
--- ---- ---- ---- ---- --
61 49 49 49 49 79
76
- -
22 22
- -
200 200
ERROR: The name is not a valid SAS name.
ERROR 61-185: The name is not a valid SAS name.
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.
ERROR 79-322: Expecting a (.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: a name, an integer constant,
arrayname, #, $, &, (, +, -, /, //, :, ;, =, ?, @, @@, OVERPRINT, [, _ALL_,
_BLANKPAGE_, _ODS_, _PAGE_, {, ~.
ERROR 200-322: The symbol is not recognized and will be ignored.
1739 put "&name_list";
1740 end;
1741
1742 put (_all_)(:);
1743 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
Would this do the job?
*Create demo data;
data class;
set sashelp.class;
label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;
proc sql noprint;
create table temp as
select name as _name_, label as _label_
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
select cats(quote(name),"n") into :varList separated by ' '
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
quit;
data _null_;
file "&sasforum.\datasets\TwoLinesHeader.csv" dsd;
set class;
if _n_ = 1 then do;
do until(eof);
set temp end=eof;
put _name_ @;
end;
put;
eof = 0;
do until(eof);
set temp end=eof;
put _label_ @;
end;
put;
end;
put (&varList) (:);
run;
Isn't option dsd (in the file statement) supposed to handle internal delimiters properly?
Since there is comma in the value as 'Age, Years', I suggest to change delimeter other than comma, for example, I use semi-comma in below codes:
1. add delimeter into quote function.
2. remove quote marks to call macro variables.
*select names into a macro variable name_list;
*Select labels into a macro variable label_list;
proc sql noprint;
select quote(trim(name)||';') into :name_list separated by ' '
from sashelp.vcolumn
where libname='WORK' and memname='CLASS' and not missing (name);
select quote(trim(coalesce(label, name))||';') into :label_list separated by ' '
from sashelp.vcolumn
where libname='WORK' and memname='CLASS';
quit;
*check macro variables;
%put Name= &name_list;
%put;
%put Label= &label_list;
data _null_;
set class;
file fout dlm=';' dsd;
if _n_=1 then
do;
put &label_list;
put &name_list;
end;
put (_all_)(:);
run;
Hope it will help
Would this do the job?
*Create demo data;
data class;
set sashelp.class;
label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;
proc sql noprint;
create table temp as
select name as _name_, label as _label_
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
select cats(quote(name),"n") into :varList separated by ' '
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
quit;
data _null_;
file "&sasforum.\datasets\TwoLinesHeader.csv" dsd;
set class;
if _n_ = 1 then do;
do until(eof);
set temp end=eof;
put _name_ @;
end;
put;
eof = 0;
do until(eof);
set temp end=eof;
put _label_ @;
end;
put;
end;
put (&varList) (:);
run;
Thanks @PGStats this one worked for what I need. The only change I ended up making was to add the COALESCEC function to take the variable name when the label is missing. Otherwise this works exactly as needed.
coalescec(label, name) as _label_
If you dont object to using GOTOs, you can get by without any macro:
*Create demo data;
data class;
set sashelp.class;
label age='Age, Years' weight = 'Weight(lbs)' height='Height, inches';
run;
proc sql;
create table temp as
select name as _name_, label as _label_
from dictionary.columns
where libname="WORK" and upcase(memname)="CLASS";
quit;
data _null_;
file "&sasforum.\datasets\TwoLinesHeader_2.csv" dsd;
set class;
if _n_ = 1 then goto headers;
put (_all_) (:);
return;
headers:
do until(eof);
set temp end=eof;
put _name_ @;
end;
put;
eof = 0;
do until(eof);
set temp end=eof;
put _label_ @;
end;
put;
return;
run;
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.