2

I'm wondering if it's possible to save the query results to csv? Without creating views.

I have a large table but need only 2 columns from there to process with python then. Maybe someone can help with it?

kptkhv
  • 23
  • 3
  • 1
    Possible duplicate of [Efficiently convert a SAS dataset into a CSV](https://stackoverflow.com/questions/3711250/efficiently-convert-a-sas-dataset-into-a-csv) – user667489 Oct 16 '19 at 09:36
  • no, I dont need to export the whole table only a few columns – kptkhv Oct 16 '19 at 09:39
  • 1
    You can use any of the options in that answer and add a keep clause to restrict the columns. Where you specify the dataset to be exported via `data=mydata` or `set mydata` you can replace `mydata` with `mydata(keep = col1 col2)`. – user667489 Oct 16 '19 at 09:59

2 Answers2

1

Here are three ways

ODS

SQL query can be output to an ODS CSV destination. This approach encompasses the widest possibilities of querying.

ods csv file='c:\temp\query-results.csv';

proc sql;
  select name, age 
  from sashelp.class
  where name like 'J%'
  ;
quit;

ods csv close;

EXPORT Procedure

Where clause can be applied using kept columns of 'a large table' (data=)

proc export 
  data = sashelp.class(
    keep=name age
    where = (
      name like 'J%'
    )
  ) 
  replace 
  file = 'c:\temp\class-subset.csv'
  dbms = csv
  ;
run;

DATA _null_

Where statement can be applied using any columns of 'a large table' (SET). The PUT statement manages which columns are output.

data _null_;
  set sashelp.class;
  where name like 'J%';

  file 'c:\temp\subset-per-datastep.csv' dlm=',' dsd;

  if _n_ = 1 then put 'name,age';
  put name age;
run;
Richard
  • 25,390
  • 3
  • 25
  • 38
  • Nice, the only other method I can think of is the `%ds2csv()` utility macro, but I'm sure there's probably other ways too... I wonder how many we're missing? – Robert Penridge Oct 18 '19 at 15:28
0

I think you can use ods to create file with results, for example:

ods csv file="C:\test.csv" options(delimiter=';');

proc sql;
   select * from sashelp.class;
quit;

ods csv close;
Llex
  • 1,770
  • 1
  • 12
  • 27