topic Re: Export with Text Qualifiers in SAS Procedures

Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 13:06:34 GMT

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

Re: Export with Text Qualifiers

Flip — Fri, 22 Jan 2010 13:20:04 GMT

data _null_;
file out dlm='|';

set xxxx;

put varname ;

run;

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 13:23:28 GMT

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

Re: Export with Text Qualifiers

Flip — Fri, 22 Jan 2010 13:29:00 GMT

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;

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 13:34:41 GMT

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;

Re: Export with Text Qualifiers

Flip — Fri, 22 Jan 2010 14:18:58 GMT

You need to close the listing before opening cvs.

So use ODS LISTING CLOSE; as your first statement.

Re: Export with Text Qualifiers

data_null__ — Fri, 22 Jan 2010 14:19:07 GMT

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]

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 14:37:00 GMT

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

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 14:37:13 GMT

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

Re: Export with Text Qualifiers

sbb — Fri, 22 Jan 2010 14:39:24 GMT

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

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 14:43:18 GMT

But what i was using is only SAS 9.1.0

Re: Export with Text Qualifiers

sbb — Fri, 22 Jan 2010 14:48:39 GMT

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

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 14:52:56 GMT

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

Re: Export with Text Qualifiers

data_null__ — Fri, 22 Jan 2010 14:59:46 GMT

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]

Re: Export with Text Qualifiers

sbb — Fri, 22 Jan 2010 15:03:45 GMT

Multiple solutions have been suggested. Also consider using the SAS-supplied DS2CSV utility macro with the SEPCHAR= parameter.

Recommended Google advanced search argument for this topic/post:

ds2csv site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Export with Text Qualifiers

deleted_user — Fri, 22 Jan 2010 15:03:47 GMT

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

Re: Export with Text Qualifiers

Flip — Fri, 22 Jan 2010 15:15:52 GMT

run http://support.sas.com/rnd/base/ods/odsmarkup/csvtags.tpl

then use tehh Proc print as I told you. This will allow the use of the delimiter in SAS 9.1

Re: Export with Text Qualifiers

data_null__ — Fri, 22 Jan 2010 15:18:04 GMT

OK then.

183  data class;
184     set sashelp.class;
185     if _n_ in(1,4,5,19) then substr(name,3,1)='|';
186     else if _n_ in(3,6,7) then call missing(of _all_);
187     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.01 seconds
      cpu time            0.01 seconds
 
 
188
189  data _null_;
190     set class;
191     file log dlm='|' dsd;
192     if _n_ eq 1 then link names;
193     put (_all_)(~);
194     return;
195  names:
196     length _name_ $32;
197     do while(1);
198        call vnext(_name_);
199        if upcase(_name_) eq '_NAME_' then leave;
200        put _name_ ~ @;
201        end;
202     put;
203     return;
204     run;
 
"Name"|"Sex"|"Age"|"Height"|"Weight"
"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.03 seconds
      cpu time            0.03 seconds

Re: Export with Text Qualifiers

Peter_C — Fri, 22 Jan 2010 15:48:35 GMT

couldn't be simpler
Suppose you have a list of the required columns in a macro variable: &columns, like for eample, from sample table sashelp.class[pre] %let columns = name age height ;[/pre] then there is a format modifier that will quote the variables[pre]data _null_ ;
file 'your.pipe.file.txt' lrecl=10000 dsd dlm='|' ;
set sashelp.class ;
put (&columns)(~) ;
run ;[/pre]
It is that tilde(~) modifier which quotes the columns.

PeterC

Re: Export with Text Qualifiers

sbb — Fri, 22 Jan 2010 17:58:05 GMT

Question back to the OP prem85:

With all the feedback you have received, what, if any, attempt have you made to exercise some of these various approaches and technical options? You keep regurgitating your "quest" but it doesn't appear you are embracing what's been provided and tell us what progress and/or success you have been able to achieve with the forum feedback.

Scott Barry
SBBWorks, Inc.