saspy: write large SAS table to local csv

Question

The SASData object has to_csv and to_df_CSV methods, but both of these write to locations on the host machine (where the SAS session is running). Is there a way to write a large SAS data table to .CSV on my local machine from a remote connection? The option on the saspy Github page was to get the DataFrame, via to_df, and then write that to .CSV, but this won't work in my case because the SAS table is bigger than memory. Must I read and write in chunks?

Answer 1

Here's an example of what I'm suggesting.

tom64-3> python3.5
Python 3.5.5 (default, Feb  6 2018, 10:56:47)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import saspy
>>> sas = saspy.SASsession(cfgname='iomjwin')
SAS Connection established. Subprocess id is 3681

No encoding value provided. Will try to determine the correct encoding.
Setting encoding to cp1252 based upon the SAS session encoding value of wlatin1.
>>> sas
Access Method         = IOM
SAS Config name       = iomjwin
WORK Path             = C:\Users\sastpw\AppData\Local\Temp\SAS Temporary Files\_TD20052_d10a626_\Prc4\
SAS Version           = 9.04.01M4P11142016
SASPy Version         = 2.4.3
Teach me SAS          = False
Batch                 = False
Results               = Pandas
SAS Session Encoding  = wlatin1
Python Encoding value = cp1252
SAS process Pid value = 20052


>>> cars = sas.sasdata('cars', 'sashelp')
>>> cars.head()
    Make           Model   Type Origin DriveTrain   MSRP  Invoice  EngineSize  \
0  Acura             MDX    SUV   Asia        All  36945    33337         3.5
1  Acura  RSX Type S 2dr  Sedan   Asia      Front  23820    21761         2.0
2  Acura         TSX 4dr  Sedan   Asia      Front  26990    24647         2.4
3  Acura          TL 4dr  Sedan   Asia      Front  33195    30299         3.2
4  Acura      3.5 RL 4dr  Sedan   Asia      Front  43755    39014         3.5

   Cylinders  Horsepower  MPG_City  MPG_Highway  Weight  Wheelbase  Length
0          6         265        17           23    4451        106     189
1          4         200        24           31    2778        101     172
2          4         200        22           29    3230        105     183
3          6         270        20           28    3575        108     186
4          6         225        18           24    3880        115     197
>>> cars.to_csv(sas.workpath+'\cars.csv')
11                                                                                                                       The SAS System                                                                                         11:15 Tuesday, February 19, 2019


99
100        options nosource;


NOTE: The file X is:
      Filename=C:\Users\sastpw\AppData\Local\Temp\SAS Temporary Files\_TD20052_d10a626_\Prc4\cars.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=19Feb2019:11:16:58,
      Create Time=19Feb2019:11:16:58

NOTE: 429 records were written to the file X.
      The minimum record length was 68.
      The maximum record length was 123.
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


428 records created in X from SASHELP.CARS.


NOTE: "X" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           1.49 seconds
      cpu time            0.21 seconds


>>> res = sas.download('/u/sastpw', r'C:\Users\sastpw\AppData\Local\Temp\SAS Temporary Files\_TD20052_d10a626_\Prc4\cars.csv')
>>> print(res['LOG'])
19                                                                                                                       The SAS System                                                                                         11:15 Tuesday, February 19, 2019


278
279        filename _sp_updn 'C:\Users\sastpw\AppData\Local\Temp\SAS Temporary Files\_TD20052_d10a626_\Prc4\cars.csv' recfm=F encoding=binary lrecl=4096
280
281
20                                                                                                                       The SAS System                                                                                         11:15 Tuesday, February 19, 2019


284
21                                                                                                                       The SAS System                                                                                         11:15 Tuesday, February 19, 2019


287
288        filename _sp_updn;
NOTE: Fileref _SP_UPDN has been deassigned.
289
290
>>>
SAS Connection terminated. Subprocess id was 3681
tom64-3> ll /u/sastpw/cars.csv
-rw-r--r-- 1 sastpw r&d 38142 Feb 19 11:18 /u/sastpw/cars.csv
tom64-3>
tom64-3> head /u/sastpw/cars.csv
Make,Model,Type,Origin,DriveTrain,MSRP,Invoice,EngineSize,Cylinders,Horsepower,MPG_City,MPG_Highway,Weight,Wheelbase,Length
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101,172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115,197
Acura,3.5 RL w/Navigation 4dr,Sedan,Asia,Front,"$46,100","$41,100",3.5,6,225,18,24,3893,115,197
Acura,NSX coupe 2dr manual S,Sports,Asia,Rear,"$89,765","$79,978",3.2,6,290,17,24,3153,100,174
Audi,A4 1.8T 4dr,Sedan,Europe,Front,"$25,940","$23,508",1.8,4,170,22,31,3252,104,179
Audi,A41.8T convertible 2dr,Sedan,Europe,Front,"$35,940","$32,506",1.8,4,170,23,30,3638,105,180
tom64-3>

Answer 2

With saspy V2.4.3, you could try to_csv() to create the csv file on the SAS server, then download(), which is new in 2.4.3, to pull the csv over to your local filesystem.