topic Executable procedure to convert SAS dataset to CSV in SAS Programming

Executable procedure to convert SAS dataset to CSV

Salmononius2 — Thu, 02 May 2019 14:55:21 GMT

I regularly have a requirement to convert files that I receive in sas7bdat format to csv. Currently, what I do is a very manual and idividualized process where I need to log into the SAS Enterprise Guide, change the filenames in the procedure, and run the procedure for each file that needs to be converted. Is there any way to set up the program where it would run automatically (or with a variabe input) that could be called from some sort ofexecutable/script?

I was thinking one of two possible solutions might work:

When the procedure is run, it scans $location for SAS datasets, and converts them to csv files.
The procedure gets a variable of $fileName fed to it (maybe when it gets called through a powershell/linux script), and proceeds to convert that file.

Is something like that feasible? My main goals are to limit the steps I need to go through when converting the files, as well as possibly making this process automatic/self-service where I wouldn't need to manually covert the files each time I receive a request.

For reference, the code that I currently use is:

libname aa '\\path\wher\file\is\located';

 

proc export data=aa.file

outfile='\\path\wher\file\is\located\file.csv'

dbms=csv 

replace;

putnames=yes;

run;

Thanks in advance! I am new to SAS, so I hope what I'm writing makes sense. If there's anything I could clarify, please let me know.

Re: Executable procedure to convert SAS dataset to CSV

ChrisNZ — Fri, 03 May 2019 05:41:23 GMT

Welcome to the SAS forum!

Both of these options are feasible.

There is plenty of help already available here about scanning a directory and getting the file names.

One of the simplest solution would be to:

1. Have a data step scan the folder for file names.

2. The same data step trigger proc import using the file name, via call execute.

You should be able to find many examples of that kind of code for both these tasks.

Re: Executable procedure to convert SAS dataset to CSV

ScottBass — Fri, 03 May 2019 07:25:18 GMT

Look here:

https://github.com/scottbass/SAS/tree/master/Macro

esp:

dirlist

export

loop

loop_control

Edit: So I read your post more closely. This should get you started:

* create list of SAS datasets (adjust to suit) ;
proc sql;
   create table datasets as
   select catx('.',libname,memname) as dataset_name
   from dictionary.tables
   where libname='SASHELP'
     and memname in ('CLASS','CARS','DEMOGRAPHICS','SHOES','STOCKS')
   ;
quit;

* create space delimited list 
* this could have been done in one step above ;
proc sql noprint;
   select dataset_name into :datasets separated by " "
   from datasets;
run;

%put &=datasets;

* create macro to export to csv ;
* either proc export or my %export macro should work ;
%macro code;
   %let filename=%scan(&word,2,.);
   %let root=C:\Temp;
   %export(data=&word, file="&root\&filename..csv")
%mend;

* loop over the list of datasets ;
%loop(&datasets)

Re: Executable procedure to convert SAS dataset to CSV

Salmononius2 — Mon, 06 May 2019 14:08:59 GMT

Thank you ChrisNZ and ScottBass! I think I'm good now with the first part, where I can loop through a directory and create a csv from each file in it (your comments helped me clarify what I needed to look for, and I was able to find this link: https://communities.sas.com/t5/SAS-Programming/Export-multiple-tables-from-SAS-to-csv/td-p/27090).

Now I'm up to the second part: Triggering the procedure from outside the SAS environment. I'm searching to see if there's anything on here already that could help (this link seems promising: https://communities.sas.com/t5/General-SAS-Programming/Users-run-Sas-program-from-CMd-line/td-p/170957). If anyone has any other suggestions, I would appreciate any ideas.

Thanks again for everyone's help!

Re: Executable procedure to convert SAS dataset to CSV

ChrisNZ — Mon, 06 May 2019 23:05:53 GMT

You are on the right track. Look here too: http://support.sas.com/kb/25/213.html

Re: Executable procedure to convert SAS dataset to CSV

ScottBass — Tue, 07 May 2019 02:07:22 GMT

@Salmononius2 wrote:
I think I'm good now with the first part, where I can loop through a directory and create a csv from each file in it

I recommend getting the list of SAS datasets from within SAS, rather than looping through a physical directory. For example, your approach would not work if you were using the SPDE engine. Instead, use the SAS dictionary tables to get your list of SAS tables.

Re: Executable procedure to convert SAS dataset to CSV

Tom — Tue, 07 May 2019 05:13:22 GMT

There are two parts to your question. To enhance your code to convert ALL of the datasets you just need to use the list of dataset to drive generating the conversion code.

%let path=\\path\wher\file\is\located;

libname aa "&path" access=readonly;
proc contents data=aa._all_ noprint out=contents; run;
data _null_;
  set contents ;
  by memname ;
  if first.memname;
  call execute(catx(' '
,'proc export data=',catx('.',libname,memname)
,cats('outfile="&path/',memname,'.csv"'
,'dbms=csv replace;'
,'putnames=yes;'
,'run;'
 ));
run;

When you use Enterprise Guide where is the SAS server that you are connecting to?

Can you run SAS on that server without using Enterprise Guide?

If so then you can create a program with the code above and then at the command prompt just use the command sas to run the program. Now you might want to modify the program to allow you to pass in the path to the files to convert. Note this would be much easier on Windows if the path was mapped to a drive letter. Then you could hard code the program to just use the current directory by just changing the %LET statement to :

%let path=.;

So then to convert all of the files in a directory you would just run these two commands:

cd \path\to\files
sas \path\to\program

Re: Executable procedure to convert SAS dataset to CSV

Onizuka — Tue, 07 May 2019 10:22:42 GMT

%macro csvOut(dataset, path, outname);

	%if &amp;OUTNAME EQ %THEN %let outname = &amp;dataset ;

		proc export
		data = &amp;dataset
		outfile=&quot;&amp;path./&amp;outname..csv&quot;
		dbms=csv
		replace;
		run;

%mend;

This is my macro for export csv, I don't know if you can use it !