topic Re: How to save a CAS in memory table to a CSV file with quoted characters. in SAS Programming

How to save a CAS in memory table to a CSV file with quoted characters.

rahulrai — Thu, 19 Sep 2019 12:35:58 GMT

I have a CAS in memory table which i want to save as a CSV file in the data source associated with the CASLIB. The tables have characters and numeric fields. I am running this code on SAS Studio in SAS Viya. Below is the code that I am using.

Using PROC CASUTIL:

proc casutil;

save casdata="tablename" incaslib="caslib" outcaslib="caslib" casout="filename.csv" replace;

run;

Using PROC CAS:

proc cas;

table.save /
table={name="tablename" caslib="caslib"}
name="filename.csv" caslib="caslib" replace="yes";

run;

Now in both the cases the result is same, i.e., the file is created as a CSV the numeric fields are coming properly without enclosing of quotes. But with the character fields, the character fields that have space in them are enclosed in quotes and the character fields without any space in them are not enclosed in quotes. Some thing like shown below:

acct_num,org,cust_num,int_status,curr_bal,block_code
0004010391500712881,400,0004010391500646024,A,5000,F
0004010391500712882,400,0004010391500712883,D,700.5,L
0004010391500712883,400,0004010391500712883,D,0,L
0004010391500712884,400,0004010391500712883,D,1000,""
0004010391500712885,400,0004010391500712883,D,1000,abcd
0004010391500712886,400,0004010391500712883,"",1000,abcd
0004010391500712887,400,0004010391500712883,"",1000,abcd

Wherein the columns int_status and block_code are character fields.

This seems like a very inconsistent behavior, or may be I am unaware of the reason.

My requirement is that the character fields should be enclosed in double quotes or at the very least none of the characters fields are enclosed in quotes. I want to achieve uniformity in how a specific type of data is written. My preference is that the characters fields should be enclosed in double quotes.

Is there is some option using which this can be achieved?

Re: How to save a CAS in memory table to a CSV file with quoted characters.

Tom — Thu, 19 Sep 2019 12:58:52 GMT

Looks like a normal CSV file, other than those places with two double quote characters next to each other.

Only values that contain the delimiter character or the quote character need to have quotes around them. Whether you think they represent numbers or strings doesn't matter in whether the quotes are needed or not.

Re: How to save a CAS in memory table to a CSV file with quoted characters.

alexal — Thu, 19 Sep 2019 13:05:37 GMT

That is normal and you can't change it.

Re: How to save a CAS in memory table to a CSV file with quoted characters.

rahulrai — Thu, 19 Sep 2019 14:18:48 GMT

Hi Tom,

Thanks for the inputs.

The Double Quotes next to each other are "spaces" in my table.

Now when you say "Only values that contain the delimiter character or the quote character need to have quotes around them" , do you mean that it takes "space" as a delimiter? Because my string does not have quotes and the delimiter for my file is "comma".

and if it considers space as a delimiter can you suggest any option which specifies that space is not a delimiter?

Many Thanks.

Re: How to save a CAS in memory table to a CSV file with quoted characters.

Tom — Thu, 19 Sep 2019 14:48:51 GMT

Normal SAS considers a character variable with only spaces in it as a missing value. So I would expect that a variable that consists only of spaces would generate nothing in the CSV file as that is the normal way to represent a missing value. Perhaps CAS interprets those types of values differently? Perhaps it supports variable length character strings? Similarly missing numeric values should be represented by no characters in the file.

Here is a simple SAS data step to demonstrate how missing values normally appear in CSV files.

504   data test;
505    set sashelp.class (obs=3);
506    if _n_=1 then age=.;
507    if _n_=2 then sex=' ';
508    file log dsd ;
509    put (_all_) (+0);
510   run;

Alfred,M,,69,112.5
Alice,,13,56.5,84
Barbara,F,13,65.3,98
NOTE: There were 3 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.TEST has 3 observations and 5 variables.