BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
24 REPLIES 24
Flip
Fluorite | Level 6
data _null_;
file out dlm='|';

set xxxx;

put varname ;

run;
deleted_user
Not applicable
But i think we will not be able to get " qualifier .
Flip
Fluorite | Level 6
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
Not applicable
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
Fluorite | Level 6
You need to close the listing before opening cvs.

So use ODS LISTING CLOSE; as your first statement.
data_null__
Jade | Level 19
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
Not applicable
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
But what i was using is only SAS 9.1.0
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
Thanks for the suggestions.Is anybody else can come with the solution with my existing software framework.
data_null__
Jade | Level 19
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
Not applicable
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".

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 24 replies
  • 12414 views
  • 0 likes
  • 5 in conversation