topic Re: Exporting to CSV and Adding a line with labels in SAS Programming

Exporting to CSV and Adding a line with labels

Reeza — Wed, 31 Jan 2018 18:58:54 GMT

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

Re: Exporting to CSV and Adding a line with labels

PGStats — Wed, 31 Jan 2018 19:23:10 GMT

Isn't option dsd (in the file statement) supposed to handle internal delimiters properly?

Re: Exporting to CSV and Adding a line with labels

MINX — Wed, 31 Jan 2018 19:58:45 GMT

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

Re: Exporting to CSV and Adding a line with labels

PGStats — Wed, 31 Jan 2018 20:06:13 GMT

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;

Re: Exporting to CSV and Adding a line with labels

PGStats — Thu, 01 Feb 2018 04:04:59 GMT

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;

Re: Exporting to CSV and Adding a line with labels

Reeza — Thu, 01 Feb 2018 16:34:52 GMT

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_