0

I pass data from SAS to Python using CSV format. Have a problem with a quoting format SAS uses. Strings like "480 КЖИ" ОАО aren't quoted, but Python csv module thinks they're.

dat = ['18CA4,"480 КЖИ" ОАО', '1142F,"""Росдорлизинг"" Российская дор,лизинг,компания"" ОАО"']
for i in csv.reader(dat):
    print(i)
>>['18CA4', '480 КЖИ ОАО']
>>['1142F', '"Росдорлизинг" Российская дор,лизинг,компания" ОАО']

The 2nd string is fine, but I need 480 КЖИ ОАО string to be "480 КЖИ" ОАО. Don't find such an option in csv module. Maybe it's possible to force proc export to quote all " chars?

UPD: Here's a similar problem Python CSV : field containing quotation mark at the beginning UPD2: @Quentin have asked for details. Here they're: I have SAS8.2 connected to 9.1 server. I download custom format data from server side with proc format cntlout=..; proc download... So i get a dictionary-like dataset <key>, <value>. Then i pass this dataset in CSV format using proc export via DDE interface to Python. But proc export quotes only strings which include delimiter (comma) as i understand. So i think, i need SAS to quote quotation marks too or Python to unquote only those strings which include commas.

UPDATE: switching from proc export via DDE to direct reading of dataset with a modified SAS7BDAT Python module hugely improved performance. And i got rid of the problem above.

Community
  • 1
  • 1
Winand
  • 2,093
  • 3
  • 28
  • 48
  • If you want advice from the SAS side, suggest you add more to the question, describing the values you have in SAS dataset, and the values you would like to write to the CSV. – Quentin Dec 19 '15 at 13:26
  • How did you ask SAS to create the CSV file? SAS would normally place quotes around a string with embedded quotes. So your problem value would appear in the CSV file as `"""480 КЖИ"" ОАО"`. – Tom Dec 19 '15 at 16:15
  • @Quentin i've added details – Winand Dec 19 '15 at 16:16
  • @Tom maybe i'm wrong somewhere, but `proc export` quotes string for me only if it includes field separator (comma). DDE really passes strings like `...,18CA4,"480 КЖИ" ОАО` and csv module gets confused.)) – Winand Dec 19 '15 at 16:23
  • Export does the same thing as a data step since it just generates a data step for you. Are you talking about using EXPORT to write to Excel or to CSV? If you write to a CSV file then the extra quotes are there. Also don't look at the CSV file using Excel since it will transform the data. Look at it with a text editor. – Tom Dec 19 '15 at 16:41
  • SAS 8.2? That is over 14 years old? Since you are paying the same annual license fee to SAS to run the old version why not upgrade to the current version? But even in SAS 8.2 it should quote embedded quotes. – Tom Dec 19 '15 at 16:52
  • @Tom a railway company i'm working in has a SAS/AF application which is soooo incovenient.) I want to use SAS as a proxy layer between data i need and my Python app. (Thanks to SAS Institute for COM interface) – Winand Dec 19 '15 at 17:10

1 Answers1

0

SAS will add extra quotes if the value has quotes in it already.

data _null_;
  file log dsd ;
  string='"480 КЖИ" ОАО';
  put string;
run;

Generates this result:

"""480 КЖИ"" ОАО"

Perhaps the quotes are being removed at some other point in the flow from SAS to Python? Try saving the CSV file to a disk and having Python read from the disk file.

Tom
  • 47,574
  • 2
  • 16
  • 29
  • I'll try it with test datasets 36 hours later.) There's one more option: reading dataset directly from temporary folder (WORK library) with sas7bdat Python module. But it needs some speed tests – Winand Dec 19 '15 at 17:23
  • Look. It quotes only with space separator! https://docs.google.com/document/d/170Akula96Aan9rrST_zH4WfwX8XieJLq25WtbTfkuYI – Winand Dec 21 '15 at 07:02
  • oh i see, it quoted all strings cause they all include space – Winand Dec 21 '15 at 07:54