Save a dataset as a CSV

[DanieleTiles]

Hi to all,
I need to save/export a lot of datasets into CSV, in order to make them available for other applications. I've been developing this SAS procedure (which will run as a batch on a *nix system when deployed), and I've encountered this problems:
1) I'm trying to export the datasets using ODS and proc print...there seems no problem, except for columns containing very long descriptions, and I get this warning message:

ODS csv file="&EXPORT_DIR.SOC_L_ESE_ESERCIZIO.csv" type=csv;

PROC PRINT data=tdmSOC.SOC_L_ESE_ESERCIZIO NOobs;
run; quit;

ODS tagsets.csv close;

WARNING: Data too long for column "CAP_DES"; truncated to 97 characters to fit.

How can I fix this?

2) When I do launch this program, I kept getting a prompt which asks me if I want to open the newly created CSV file...how can I avoid that? Through the -noterminal option to pass to SAS.exe?

Thanks

Daniele

[Cynthia_sas]

Hi:
The file truncated message has been linked to PROC IMPORT, as explained here:
http://support.sas.com/kb/5/492.html

I cannot duplicate your error message. I don't know how big your text field is, but I made a field larger than 200 and another field of $1000 and didn't have any issues. See the Log below.
[pre]

2652 data class;
2653 length long $35 temp really_long $275 r_r_long $1000;
2654 set sashelp.class;
2655 long = 'supercalifragilisticexpialidocious';
2656 temp = 'Twas brillig and the slithy toves '||
2657 'Did gyre and gimble in the wabe.x '||
2658 'All mimsy were the borogoroves, x '||
2659 'And the mome raths outgrabe. xx x ';
2660 really_long = catt(temp,temp);
2661 r_r_long = catt(temp,temp,long,temp,temp);
2662 run;

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


2663
2664 ods _all_ close;
2665 options linesize=256;
2666 ODS csv file="ESERCIZIO4.csv" type=csv;
NOTE: Writing CSV Body file: ESERCIZIO4.csv
2667
2668 PROC PRINT data=class NOobs;
2669 run;

NOTE: There were 19 observations read from the data set WORK.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.15 seconds
cpu time 0.01 seconds


2670
2671 ODS csv close;

[/pre]
I suggest that you contact Tech Support on this issue.

Usually the rule is that you have to close the destination using the same destination reference you used when you opened the destination. I would recommend that you clean up your ODS invocation using one of the invocations, as shown:
[pre]
** 1) Use the tagset template alias method;
ods csv file='ex1.csv';
proc print data=sashelp.class;
run;
ods csv close;

** 2) Use the ODS MARKUP method;
ods markup type=csv file='ex2.csv';
proc print data=sashelp.class;
run;
ods markup close;

** 3) Use the ODS TAGSETS.xxx method;
ods tagsets.csv file='ex3.csv';
proc print data=sashelp.class;
run;
ods tagsets.csv close;

[/pre]

#1 works because ODS CSV is one of the "aliased" destinations that you can invoke by using the alias name -- for this invocation, the proper close is: ODS CSV CLOSE. Many ODS destinations have aliases. For example, ODS HTML is an alias for ODS TAGSETS.HTML4 and ODS MSOFFICE2K is an alias for ODS TAGSETS.MSOFFICE2K. Not all ODS MARKUP tagset templates have aliases, you can find the list of aliased destinations here:
http://support.sas.com/documentation/cdl/en/odsug/59523/HTML/default/a002217090.htm#a002588754

#2 works because you are invoking the ODS MARKUP destination with the TYPE= option -- for this invocation, the proper close is: ODS MARKUP CLOSE.

#3 works because you can always refer to the full name of the tagset template as a destination: ODS TAGSETS.CSV (or ODS TAGSETS.EXCELXP or ODS TAGSETS.MSOFFICE2K are other examples) -- the proper close for this invocation is ODS TAGSETS.CSV CLOSE.

If you use an improper close, it is possible (although not likely in this case), that the operating system won't properly close the file that's being created. I've seen this happen more often when creating multiple HTML or multiple RTF or multiple PDF files with improper closing syntax. You can always use ODS _ALL_ CLOSE; at the end of an ODS invocation to close ALL open destinations....no matter how you invoked them. Also, PROC PRINT is not an interactive procedure....it does not require a QUIT; as the final step boundary. You would need to put a QUIT after SAS/Graph syntax (not relevant in this case) or PROC SQL syntax (also not relevant in this case) or any interactive STAT procedure that uses QUIT and not RUN as the step boundary.

I suspect that you are getting prompted to open the CSV file because of a registry issue, such as that described here for RTF:
http://support.sas.com/kb/9/627.html

It doesn't make sense to me that you are getting this popup window if you're running this code as a batch job. Are you by any chance doing testing in SAS Enterprise Guide??? At any rate, I'd recommend working with Tech Support on this issue, too.

Also, just a terminology note...ODS CSV is not "technically" doing an EXPORT. When PROC EXPORT does an EXPORT, it uses a different mechanism to create the CSV or Excel file than ODS CSV uses. ODS CSV is creating an ASCII text file that is in CSV format. You can use PROC EXPORT only on a SAS dataset. However, you could ALSO send the results from PROC FREQ, PROC MEANS or PROC GLM to a CSV file using ODS CSV. When you use PROC PRINT with ODS CSV, you are sending the results of using PROC PRINT to an ASCII text file. ODS CSV doesn't care if you send PROC PRINT or PROC GLM results to a CSV file; on the other hand, PROC EXPORT would only work on a SAS dataset.

cynthia

[Patrick]

Hi Daniele

An alternative could be to use some of Richard A. DeVenezia's macros:
http://www.devenezia.com/downloads/sas/macros/index.php?m=ds2text

HTH
Patrick

[DanieleTiles]

Hi Cynthia,
thanks for your kind reply...no, I've been developing this code in SAS Base, without using EG.
The tip about the prompting seems to be working, since I don't get the download prompt anymore.
Anyway, I'm still a bit confused about the fact this problem might be correlated to the PROC IMPORT...since I didn't import anything (this dataset is a final result of many data and proc sql steps, and it started from an Oracle table...)...
To make an example, one of the datasets has got the system to prompt me the warning on the column "VCR_DES", which is just $150...

[DanieleTiles]

Solved, on a different way.
The problem wasn't the ODS csv, but instead a limit of proc print...so I followed this guide:

http://www2.sas.com/proceedings/sugi29/042-29.pdf

Page 3:

"When you have a long character variable (such as a COMMENT field in the questionnaire), and you want to print the values using PROC PRINT, you will get the warning message:
WARNING: Data too long for column "COMMENT"; truncated to 124 characters to fit.
A simple solution is to use PROC SQL with the flow option."

This worked smoothly 🙂
Thanks for your help

Daniele

[Cynthia_sas]

Hi:
I'm stumped. I don't see that behavior from PROC PRINT with a character field the length of $1000 or with a length of $4000. Just how big -is- your character field? I tried the $4000 code in SAS 9.1.3 and in SAS 9.2. I don't doubt that you are getting the message, I just think that it's very odd that the error message is not easily replicated.

At any rate, I'm glad you found something that worked for your needs.

cynthia

[Cynthia_sas]

Hi:
An update. It seems the problem is related to this Tech Support note -- most frequently seen with PROC REPORT:
http://support.sas.com/kb/18/518.html

I believe that if you close the listing destination before your ODS CSV step, you will no longer get the "truncated" warning. I believe the warning comes from the LISTING window trying to print/display a column that is bigger than the current value of the LINESIZE. For example, if I do this then I get the message:
[pre]
45 options linesize=80;
46 ods listing;
47 ODS csv file="ESERCIZIO4s.csv" type=csv;
NOTE: Writing CSV Body file: ESERCIZIO4s.csv
48
49 PROC PRINT data=danielle.Soc_l_vcr_voceerogante NOobs;
50 run;

WARNING: Data too long for column "VCR_DES"; truncated to 80 characters to fit.
WARNING: Data too long for column "VCA_DES"; truncated to 80 characters to fit.
NOTE: There were 1810 observations read from the data set
DANIELLE.SOC_L_VCR_VOCEEROGANTE.
NOTE: PROCEDURE PRINT used (Total process time):
real time 1.45 seconds
cpu time 1.29 seconds


51
52 ODS csv close;

[/pre]

Note how the size of the truncation coincides to the current value of LINESIZE.

But, if I take ODS LISTING window out of the picture and submit this code, (note the ODS LISTING CLOSE;), then I do not get the warning.
[pre]
54
55 options linesize=80;
56 ods listing close;
57 ODS csv file="ESERCIZIO4xxx.csv" type=csv;
NOTE: Writing CSV Body file: ESERCIZIO4xxx.csv
58
59 PROC PRINT data=danielle.Soc_l_vcr_voceerogante NOobs;
60 run;

NOTE: There were 1810 observations read from the data set
DANIELLE.SOC_L_VCR_VOCEEROGANTE.
NOTE: PROCEDURE PRINT used (Total process time):
real time 1.34 seconds
cpu time 1.26 seconds


61
62 ODS csv close;

[/pre]

cynthia
(code tested using Daniele's data set)

[DanieleTiles]

Hi Cynthia,
yes, it was definitely the listing: with your suggestion I've switched back to proc print (which in MUCH faster than proc sql when printing).
Thanks for your tip!

Daniele Message was edited by: Daniele Tiles