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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.