Help using Base SAS procedures

Export with Text Qualifiers

Reply
N/A
Posts: 0

Export with Text Qualifiers

I need to export a SAS dataset to a text file with pipe delimieter (|) and with " as text qualifier.Please help me to solve the problem
Super Contributor
Posts: 359

Re: Export with Text Qualifiers

Posted in reply to deleted_user
data _null_;
file out dlm='|';

set xxxx;

put varname ;

run;
N/A
Posts: 0

Re: Export with Text Qualifiers

But i think we will not be able to get " qualifier .
Super Contributor
Posts: 359

Re: Export with Text Qualifiers

Posted in reply to deleted_user
Oh I see you want text quoted.

Use the new CSV tagset set the delimiter to '|'

Then just do a proc print;

ods csv filename (deliimiter = '|');

proc print;

ods csv close;
N/A
Posts: 0

Re: Export with Text Qualifiers

I tried and got the following error


ods csv close;

ods csv FILE='C:\Documents and Settings\premkumar\Desktop\class_p.txt' (delimiter = '|');

---------
22
76
ERROR 22-322: Syntax error, expecting one of the following: DYNAMIC, INCLUDE, NO_BOTTOM_MATTER,
NO_TOP_MATTER, TITLE, URL.
ERROR 76-322: Syntax error, statement will be ignored.

proc print data=tmp1.lot;
run;

: There were 7574 observations read from the data set TMP1.LOT.
: PROCEDURE PRINT used (Total process time):
real time 0.10 seconds
cpu time 0.07 seconds



ods csv close;
Super Contributor
Posts: 359

Re: Export with Text Qualifiers

Posted in reply to deleted_user
You need to close the listing before opening cvs.

So use ODS LISTING CLOSE; as your first statement.
Respected Advisor
Posts: 3,799

Re: Export with Text Qualifiers

Posted in reply to deleted_user
The only time you "need" the qualifier is when the data contain the delimiter. SAS will quote the value when needed as this example when run will denomstrate.

[pre]
data class;
set sashelp.class;
if _n_ in(1,4,5,19) then substr(name,3,1)='|';
run;
data _null_;
set class;
file log dsd dlm='|';
put (_all_)(Smiley Happy;
run;

filename test temp;
proc export data=class outfile=test replace dbms=csv;
delimiter='|';
run;

data _null_;
infile test;
input;
list;
run;
[/pre]
N/A
Posts: 0

Re: Export with Text Qualifiers

Posted in reply to data_null__
The example u have put forward makes the first variable with in " .
But what i need exactly is

"name"|"age"|"sex"|"class".
as something i mentioned above
N/A
Posts: 0

Re: Export with Text Qualifiers

Posted in reply to data_null__
The example u have put forward makes the first variable with in " .
But what i need exactly is

"name"|"age"|"sex"|"class".
as something i mentioned above
Super Contributor
Super Contributor
Posts: 3,174

Re: Export with Text Qualifiers

Posted in reply to data_null__
Starting with SAS 9.2, the DELIMITER= parameter within OPTIONS(...) is honored by SAS and is not ignored.

Oddly enough, the SAS-generated quoted strings for CHARACTER variables are only quoted "sometimes." Using SASHELP.CLASS, with DELIMITER="\", the column/variable NAME is not quoted yet the column SEX is quoted, both the header row and the data rows. Even when I used PROC PRINT with LABEL and created a blank-spaced label for NAME, still no quote-marks around the header.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search argument for this topic/post:

ods csv options delimiter 9.2 site:sas.com Message was edited by: sbb
N/A
Posts: 0

Re: Export with Text Qualifiers

But what i was using is only SAS 9.1.0
Super Contributor
Super Contributor
Posts: 3,174

Re: Export with Text Qualifiers

Posted in reply to deleted_user
The ODS CSV override is not supported until SAS 9.2 -- as mentioned, the parameter is ignored.

You will want to explore the DATA step approach.

And, you will want to get current on SAS software.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb
N/A
Posts: 0

Re: Export with Text Qualifiers

Thanks for the suggestions.Is anybody else can come with the solution with my existing software framework.
Respected Advisor
Posts: 3,799

Re: Export with Text Qualifiers

Posted in reply to deleted_user
Does this meet your unconventional requirement?

[pre]
562 data class;
563 set sashelp.class;
564 if _n_ in(1,4,5,19) then substr(name,3,1)='|';
565 else if _n_ in(3,6,7) then call missing(of _all_);
566 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


567 data _null_;
568 set class;
569 file log dlm='|';
570 put (_all_)(~);
571 format _char_ $quote100.;
572 run;

"Al|red"|"M"|14|69|112.5
"Alice"|"F"|13|56.5|84
""|""|.|.|.
"Ca|ol"|"F"|14|62.8|102.5
"He|ry"|"M"|14|63.5|102.5
""|""|.|.|.
""|""|.|.|.
"Janet"|"F"|15|62.5|112.5
"Jeffrey"|"M"|13|62.5|84
"John"|"M"|12|59|99.5
"Joyce"|"F"|11|51.3|50.5
"Judy"|"F"|14|64.3|90
"Louise"|"F"|12|56.3|77
"Mary"|"F"|15|66.5|112
"Philip"|"M"|16|72|150
"Robert"|"M"|12|64.8|128
"Ronald"|"M"|15|67|133
"Thomas"|"M"|11|57.5|85
"Wi|liam"|"M"|15|66.5|112
NOTE: There were 19 observations read from the data set WORK.CLASS.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
[/pre]
N/A
Posts: 0

Re: Export with Text Qualifiers

Posted in reply to data_null__
My task is to prepare a file with pipe delimited and " as text qualifier to load in to SQL server.Our Code is written so to accept the file in that format alone (as conveyed by our DB)
ex:

"name"|"sex"|"age"|"salary"
"deena"|"M"|"24"|"23400".
Ask a Question
Discussion stats
  • 24 replies
  • 6133 views
  • 0 likes
  • 5 in conversation