Export with Text Qualifiers

[deleted_user]

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

[Flip]

data _null_;
file out dlm='|';

set xxxx;

put varname ;

run;

[deleted_user]

But i think we will not be able to get " qualifier .

[Flip]

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;

[deleted_user]

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;

[Flip]

You need to close the listing before opening cvs.

So use ODS LISTING CLOSE; as your first statement.

[data_null__]

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_)(:);
run;

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

data _null_;
infile test;
input;
list;
run;
[/pre]

[deleted_user]

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

[deleted_user]

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

[sbb]

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

[deleted_user]

But what i was using is only SAS 9.1.0

[sbb]

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

[deleted_user]

Thanks for the suggestions.Is anybody else can come with the solution with my existing software framework.

[data_null__]

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]

[deleted_user]

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".