BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

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

PG
MINX
Obsidian | Level 7

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

PGStats
Opal | Level 21

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;
PG
Reeza
Super User

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_
PGStats
Opal | Level 21

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;
PG

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3636 views
  • 7 likes
  • 3 in conversation