Bulk download sas7bdat, convert to csv and store in local

[nivelw]

Hi all,

Hope I am raising my query at a right place.

I use SAS for some data cleansing and then generate respective outputs in SAS work library. All these are done entirely in SAS. Now I am trying to place this data cleansing step as a part of my pythonic workflow. Ideally, in python, I will use saspy to clean data and then download these clean data into local as csv so I can read them into python later without needing to run saspy everytime.

So far I managed to read my data cleansing sas code in .sas format into python and execute this .sas via saspy.submit(). By specifying specific cleansed sas dataset, I am able to use saspy.sasdata(), saspy.to_df() and then saspy.to_csv() in that sequence to download cleanse data sets one-by-one to my local.

I currently have about 20 cleanse data and I would like to "bulk" download them to local and store as csv. Instead of doing it file-by-file, would like to know if there's a more efficient way such as specifying multiply sas datasets in one single saspy.sasdata() or even use saspy to download only the .sas7bdat in saspy.dirlist(saspy.workpath)?

Appreciate any insights.

[tomweber-sas]

Glad you're finding this useful, and you have the process working. I do have one question about the flow you described. I'm assuming the following; your SAS server is remote, and your cleansing code is SAS code, so the cleansing is happening in SAS. I think that's all correct from what you are describing. Sounds like your clean data sets end up in WORK.

The one question is about where you are saying you use to_df() and to_csv(). SASData.to_df() will pull the dataset over from SAS to your client side in Python as a Pandas dataframe while SASData.to_csv() will use proc export on SAS to write out a .csv version, but that is on the server side. You could then use SASSession.download() to download that csv file to your client machine; directly to disk, not pulling it into Python. Pandas.write_csv(), would be a way to have Pandas write the dataframe from Python to your local filesystem as a .csv file, after you created the dataframe with sd2df() (to_df()). So, I'm thinking you're using Pandas to write out the csv file, not SAS, since you didn't mention using the download() method?

As for doing any of this 'in bulk' for multiple files, whether it be sd2df, to_csv, or download, there's no a method that somehow works on some group of data sets or files. Given this is Python, it's just too easy to do that with on or two lines of code, specific to your use case. You can get the list of datasets you want to either pull over as dataframes, or files to download and simply iterate over that list calling the method in question.

For instance, these 2 statements would give you the list of sas datasets in WORK, for either sd2df() methods to use, or the download() method to use:

[x[0] for x in sas.list_tables('work') if x[1] == 'DATA']

[x for x in sas.dirlist(sas.workpath) if x.endswith('.sas7bdat')]

If you used SASData.to_csv() then you would be downloading the .csv files, so get those filenames:
[x for x in sas.dirlist(sas.workpath) if x.endswith('.csv')]

You could simply iterate over those lists calling sd2df() or download(), depending upon which path you were trying to use (Having SAS write out csv on the server side, or pandas write it out on the client side). You could try both and see which one is faster, or gets you the .csv file that works the best.

>>> sd = sas.df2sd(df); sd
Libref  = WORK
Table   = _df
Dsopts  = {}
Results = Pandas

>>> [x[0] for x in sas.list_tables('work') if x[1] == 'DATA']
['_DF']
>>> [x for x in sas.dirlist(sas.workpath) if x.endswith('.sas7bdat')]
['_df.sas7bdat']

log = sd.to_csv()
>>> [x for x in sas.dirlist(sas.workpath) if x.endswith('.csv')]
['_df.csv']
>>>

Tom

[nivelw]

Hi Tom,

Thanks for your prompt and detail reply. You are right to say that the SAS data is located on a SAS server and I am attempting to get them into local.

Adding to my my post earlier, I have use script below for better clarity.

>>> df = sas.SASData('sd','work').to_df()
>>> df.to_csv(<local_path>)

Just to share, I've tried over the weekend, before checking your reply, to use instead sd2df_CSV and they worked as well. Apart from having some minor python warning messages, I managed to get the data into local folder intact. Specifically, my script below took about 4 hours for a total 700MB worth of CSV. I am guessing that may due to bottleneck at the server, VPN connection as well as my local machine's limitation.

from pathlib import Path    
import saspy

yyyymm = "202108_test"
csv_export_path = Path(path_main,"data/sasexport",yyyymm)

sas = saspy.SASsession()

data_in_server = sas.list_tables('work')
for row in data_in_server:
    globals()[row[0]] = sas.sd2df_CSV(row[0],'work')
    globals()[row[0]].to_csv(Path(csv_export_path,row[0]+".csv"))

Would like to confirm the following with respect to my script above, especially the last two lines.

1. sd2df_CSV firstly converts sas data into csv at local/client memory then the csv is read into local python environment as dataframe.
2. to_csv seems to generate and save the csv to local path from the dataframe. This seems to be different from your explanation that it uses PROC EXPORT at server side. To investigate further, I tried having, say WORK.sd generated from within SAS. I am able to do log = sas.sasdata('sd') but I can't seemed to get log.to_csv() to work. Python complained saying that TypeError: to_csv() missing 1 required positional argument: 'file'. I had to specify a local path for to_csv().

Lastly, I note the function SASSession.download() to download csv generated at SAS server. I can't find this on the saspy documentation. Would you mind illustrating how can this be done with an example?

Thanks in advance.

[tomweber-sas]

Thanks, seeing the code helps. And, I'm afraid I left off a parameter on the to_csv() I hand typed in above; you do have to provide the file, as you said in number 2.
it should have been:

log = sd.to_csv(sas.workpath+sd.table+'.csv')
>>> [x for x in sas.dirlist(sas.workpath) if x.endswith('.csv')]
['_df.csv']

I think there may be some confusion on the various csv methods. There is a to_csv() method on both the Pandas DataFrame object, as well as the SASData object. There's also a _CSV version of sasdata2dataframe, which isn't really solving anything for you in this case. That might just confuse things more.

In your code above:

data_in_server = sas.list_tables('work')
for row in data_in_server:
    globals()[row[0]] = sas.sd2df_CSV(row[0],'work')
    globals()[row[0]].to_csv(Path(csv_export_path,row[0]+".csv"))

You don't have any SASData objects, you are using sasdata2dataframe (the _CSV version, but I would just use the regular version) to create Pandas DataFrames from the SAS data set and then using Pandas to_csv() method to write the DataFrame to a csv file (that is obviously happening on the client, as it's Python).

The other case I was mentioning w/ download(), which is in the API doc, along with all the other saspy methods (https://sassoftware.github.io/saspy/api.html# scroll down, the methods are alphabetical) would use the SASPy methods to write a .csv file out on the SAS server, using proc export, and then the download() method to copy that server side csv file to your client filesystem. The different being who created the .csv file: Pandas or SAS.

So, here's the two cases, based upon your code. They will be 'equivalent', though possibly not identical.

data_in_server = sas.list_tables('work')
for row in data_in_server:
    globals()[row[0]] = sas.sd2df(row[0],'work')  # I would use the default case, MEM, as CSV isn't buying you anything here
    globals()[row[0]].to_csv(Path(csv_export_path,row[0]+".csv"))

# and here's the download version using SAS to create the csv file on the SAS side, and downloading it to the client
data_in_server = sas.list_tables('work')
for row in data_in_server:
    log = sas.write_csv(sas.workpath+row[0]+'.csv', row[0], 'work')
    # if you had a SASData object it would use to_csv(), but that's just more work, so the above line is better in this case:
    # sd = sas.sasdata(row[0],'work'); sd.to_csv(sas.workpath+sd.table+'.csv')
    log2 = sas.download(Path(csv_export_path,row[0]+".csv"), sas.workpath+row[0]+'.csv') # this downloads the csv file

I haven't run that code, just hand typed it in, so I hope I didn't make any mistakes like before. And, I don't know which case will be better for you, or if there's no significant difference. Just showing the possibilities.

Hope this is more clear,
Tom

[nivelw]

Hi Tom,

Your codes worked. Thanks a lot. One issue is that the entire extraction of 21 sas7bdat took 10 hours and it returned some error relating to com_error. I am guessing that's due to VPN connection timeout.

I am currently exploring using parquet instead of CSV. I am quite new in this. Before I dive into it, what's your view to have CSV at server side then save to local as parquet? Or if at all there's a cheaper and efficient way to get data from WORK into local?

Thanks again.

[tomweber-sas]

What access method are you using? And, what version of saspy? And, which method did you use; sd2df/pandas.to_scv, or SAS.to_csv/download?

[jld23]

Also what is the size of the sas7bdat files? Part of this is just the physics of moving data over the network.

[nivelw]

@tomweber-sas I am currently running on saspy 3.7.4. I tried both sd2df/pandas.to_csv and SAS.to_csv/download. Both came out to be similar. They took about 8-10 hours for a total 700MB worth of CSV. The sd2df/pandas.to_csv returned a couple complain about data dtype while the SAS.to_csv/download returned a com_error towards the end of the 10 hours. I am guessing this may be due VPN connection reset at my end. I've embeded below the entire com_error message. I am sure you can appreciate it better.

@jld23 I have a total of 21 sas7bdat and three of them at max 400MB with compress = yes. Current workflow is to manually export these sas7bdat into local CSV by using export dropdown menu in SAS EG. We have a project running in python recently so I was exploring the idea to automate this manual process and make it part of our Python script. After reading a bit more online, I find the conventional way, ie manual extractioin via SAS, may sit me beeter. One is the relatiely small data sets that I am dealing with and the other is network limitation behind corporate VPN conncetion.

Below is the com_error that I've received

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147417848), None)
---------------------------------------------------------------------------
com_error                                 Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_10028/2999556978.py in <module>
      1 for row in data_in_server:
      2     log  = sas.write_csv(sas.workpath+row[0]+'.csv', row[0], 'work') # export to csv at server side
----> 3     log2 = sas.download(Path(csv_export_path,row[0]+".csv"), sas.workpath+row[0]+'.csv') # this downloads the csv file

~\AppData\Local\Programs\Python\Python39\lib\site-packages\saspy\sasbase.py in download(self, localfile, remotefile, overwrite, **kwargs)
   1176             return None
   1177         else:
-> 1178             log = self._io.download(localfile, remotefile, overwrite, **kwargs)
   1179 
   1180         self._lastlog = self._io._log[lastlog:]

~\AppData\Local\Programs\Python\Python39\lib\site-packages\saspy\sasiocom.py in download(self, local, remote, overwrite, **kwargs)
    944 
    945         with open(local_file, 'wb') as f:
--> 946             f.write(self._getfile(remote))
    947 
    948         return {'Success': True,

~\AppData\Local\Programs\Python\Python39\lib\site-packages\saspy\sasiocom.py in _getfile(self, fname, buf, decode)
    323         result = bytes(flushed)
    324         while flushed:
--> 325             flushed = stream.Read(buf)
    326             result += bytes(flushed)
    327 

~\AppData\Local\Programs\Python\Python39\lib\site-packages\win32com\client\dynamic.py in Read(self, RequestedLength, BinaryData)

~\AppData\Local\Programs\Python\Python39\lib\site-packages\win32com\client\dynamic.py in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    312 
    313         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
--> 314                 result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    315                 return self._get_good_object_(result, user, resultCLSID)
    316 

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147417848), None)

[tomweber-sas]

You're using the COM access method. Yes, can you try using the IOM access method instead. It's much faster and more reliable.

[nivelw]

I tried winiomwin with SAS.to_csv/download method and it took 10 mins to download 21 CSV 700MB in total. Thanks for this really good idea. This definitely helps.

[tomweber-sas]

10 min is a big improvement over 10 hours! Yeah, the COM access method was user contributed and uses a 3rd party windows com aodb package that I have no control over. How it works is completely different that the 3 access methods I wrote, which I have more functionality and performance control over. You should be good w/ IOM.
Thanks,
Tom

[nivelw]

I've went away and tried for a few occassions. The IOM connection has been consistent and fast. Results always appear instantenously. Thanks again for the great tips and approach to help my use case work.

[tomweber-sas]

Sure thing. Do you want to mark this as the 'answer' to show this resolved? This started as a discussion, but sorta turned into an issue. At least it'll show resolved here, if that matters.
Thanks,
Tom