unix command to convert sas7bdat file to csv file?

[lserlohn]

Hello, I am a very new SAS user. I need to convert a 4Gb sas7bdat file to csv file.

I have already tried python, but it generates many unrecognizable characters each row.

I have tried R, but it says it does not support such big file.

Now, the only way seems to be using SAS Unix command to convert the file.

However, I only have a remote SAS Unix server, and I know nothing about SAS and Unix SAS command.

I see some website mentioned that write a Proc, but is there a step by step instruction on how to write and how to execute?

Thanks so much.

Lei

[SASKiwi]

A good starting point is the SAS EXPORT procedure:

libname MySASLib "/Folder1/Folder2/SASData";

PROC EXPORT Data  = MySASLib.MySASDataset

            FILE = "/Folder1/Folder2/CSV/MySASDataset.csv"

            DBMS = CSV

            REPLACE

            ;

RUN;

Modify this code to match your SAS dataset and CSV file, save it as a text file on the SAS server with a .SAS extension (eg MySASProg,sas).

Then at a Unix command line try: sas -sysin '/MySASFolder/MySASProg.sas'   - this should run your SAS program and write the CSV file.

[lserlohn]

hello, thanks for your detail instruction.

I have created such file and execute it on Unix. However, I got a error:

1          libname MySASLib "abc.sas7bdat";

ERROR: Library MYSASLIB is not in a valid format for access method RANDOM.

Does that mean the file has been opened and not valid for read at this time? Thanks.

[SASKiwi · Accepted solution]

Your LIBNAME statement must reference the system folder/directory in which your SAS dataset is stored, not the name of the SAS dataset file:

libname MySASLib "/Folder1/Folder2";

[Tom]

You do not need to define a LIBREF for this problem. Just use the quoted filename as the value of the DATA= parameter for PROC EXPORT. You do not need to include any path names ("folder" names) if the files are in the current directory.  Also when running a SAS program from the Unix prompt there is no need to include -SYSIN option.  Just use:  sas filename.  Note that if your filename includes spaces or other special characters then Unix will want you to use quotes around the name.

PROC EXPORT Data  = "abc.sas7bdat"

            FILE = "abc.csv"

            DBMS = CSV

            REPLACE

;

RUN;

[lserlohn]

Hello,

        Thanks for your reply. I renamed my file name to abc.sas7bdat, in order to avoid any problem. Also, I copied my sas file into the save folder where the abs.sas7bdat exists.

        I copied exactly the same code as yours and saved as a file named sas2csv.sas, and run it using the unix command line:  sas sas2csv.sas.

PROC EXPORT Data  = "abc.sas7bdat"

            FILE = "abc.csv"

            DBMS = CSV

            REPLACE

;

RUN;

      Now, I got an error:

1          PROC EXPORT Data  = "abc.sas7bdat"       ERROR: ""abc.sas7bdat"" is not a valid name.

      I don't understand why it is not valid............

[LinusH]

The extension .sas7bdat is a part of the physical name. When programming in SAS, you use a logical name, which happens to first, the name of the libref, and second the dataset name (the file name without the extension).

Renaming the file was totally unnecessary.

Carefully look at SASKiwi's example, it's all there...

[Tom]

Perhaps PROC EXPORT is too stupid to support that syntax. It does have problems with a number of normal SAS syntax issues.

Personally I usually just use a DATA step to write CSV files.  Especially if you do not need the column headers.

data _null_;

  set "abc.sas7bdat";

  file "abc.csv" dsd lrecl=30000 ;

  put (_all_) (:);

run;

Note that on Unix the filename for a SAS dataset must be in lower case.  So ABC.sas7bdat will not work.  SAS will not see it since it will be looking for abc.sas7bdat and on a Unix system those are two different file names.