Desktop productivity for business analysts and programmers

How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

I have a txt file that was created by an ftp export of a SAS dataset that resides on a Mainframe tape (we also tried DASD).  The txt file is in binary, not ascii.  I want to read this SAS data set into SAS EG.  Can this be done, and if so, how?   

 

I have also tried exporting from mainframe specifying the file be in sas7bdat format, but when I try to open it in SAS EG, I receive the error "The open data operation failed.  The following error occurred.  Unable to open mysasdata.sas7bdat."


Accepted Solutions
Solution
a week ago
Super User
Posts: 6,543

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

Here's the place you should be looking, to write out the mainframe file:

 

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#cport-overview.htm

 

Similarly, there is a PROC CIMPORT to read the file after it is delivered.

View solution in original post


All Replies
Solution
a week ago
Super User
Posts: 6,543

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

Here's the place you should be looking, to write out the mainframe file:

 

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#cport-overview.htm

 

Similarly, there is a PROC CIMPORT to read the file after it is delivered.

Occasional Contributor
Posts: 5

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to Astounding

I should have also mentioned that we need to implement the methodology in SAS EG.  The idea behind this effort is NOT to have to log into mainframe, so we are running an ftp command from within SAS EG that goes and grabs the mainframe file and puts it in a directory.  Can I use these SAS PROCS from the SAS EG end, after we FTP the file out of mainframe?

Super User
Posts: 6,543

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

No, that's not feasible in any way that I am aware of.  What would work:  Run a mainframe job to put the data into a format that can be interpreted by other machines/operating systems.  Transfer that form of the data.  Then from EG, from a job that takes the transferred data and brings it back into a SAS data set.

Occasional Contributor
Posts: 5

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to Astounding

Right, like in a delimited flat file, which we do have working successfully.  We were trying to avoid having to do mainframe work, and just grab mainframe SAS datasets out of mainframe and somehow read them into SAS EG.  But it is looking like that cannot be done...

Respected Advisor
Posts: 4,557

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

@saslover15 wrote:

Right, like in a delimited flat file, which we do have working successfully.  We were trying to avoid having to do mainframe work, and just grab mainframe SAS datasets out of mainframe and somehow read them into SAS EG.  But it is looking like that cannot be done...


@saslover15

SAS EG is the client. Not sure what's configured on your Mainframe but technically using the EG client to connect to the Mainframe and execute code there is very possible.

If you've got SAS/Connect licensed on both the source Mainframe and target Windows/Unix/Linux server then you can also run code (out of EG) from the target server accessing the source Mainframe server - via rsubmit; eventually just defining a library on the Mainframe side in the rsubmit block and then pulling the data directly to the target server via remote library services.

Super User
Super User
Posts: 7,860

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

FTP is not an EXPORT function. It is a file transfer protocol.  If you use it to copy a file from one system to another you probably want to make sure to move the file as BINARY and not TEXT.

 

What is in the file?

If it is a SAS dataset then you need to read it on same platform (OS/CPU type/SAS version). Especially if moving from an IBM mainframe to a PC or Unix system.

 

If it is a text file do you know how it is formatted?  You might just need to use the EBCDIC and other informats to read the data properly.

If it is a SAS transport file (either made with PROC CPORT or using the XPORT engine) then you should be able to import it.

 

Try running a little data step like this to look at the first 500 bytes of the file.  That will help you figure out what is in it.

data _null_;
  infile 'myfilename' lrecl=50 recfm=f obs=10 ;
  input;
  list;
run;
Occasional Contributor
Posts: 5

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

I am trying this and it isn't quite working...on the mainframe side, I have a sas dataset on TAPE - not flat file - and this is the code I am running in the mainframe:
//SASFILE DD DSN=MYID.SASDATA.TAPE, DISP=SHR
//OUTPUT1 DD DSN=MYID.TEST0516.OUTFILE3,                      
//  DISP=(NEW,CATLG),                                         
//  UNIT=TAPE90,LABEL=RETPD=60                                
//* UNIT=SYSDA,SPACE=(CYL,(20,20),RLSE),MGMTCLAS=MCRET60      
//SYSIN   DD *                                                
 DATA TEMP;                                                   
 SET SASFILE.SELCSEG;                                         
 RUN;                                                         
                                                              
 PROC CPORT DATA=TEMP                                         
            FILE=OUTPUT1;                                     
RUN;                                                                            

Next, I am going to SAS EG (Linux platform), and am running this code:

%ftp_remote (ftp -v  &prj_rmt_host << cmd,binary,
lcd /mydir/,
get 'MYID.TEST0516.OUTFILE3' cport.dat,
cmd);

LIBNAME mylib "/mydir";
filename convert '/mydir/cport.dat';
proc cimport data=mylib.converted infile=convert;
run;

But this does not work.  Does anyone have any ideas?

I am trying this and it isn't quite working...on the mainframe side, I have a SAS dataset on TAPE - not flat file - and this is the code I am running in the mainframe:


//SASFILE DD DSN=MYID.SASDATA.TAPE, DISP=SHR
//OUTPUT1 DD DSN=MYID.TEST0516.OUTFILE3,                      
//  DISP=(NEW,CATLG),                                         
//  UNIT=TAPE90,LABEL=RETPD=60                                
//* UNIT=SYSDA,SPACE=(CYL,(20,20),RLSE),MGMTCLAS=MCRET60      
//SYSIN   DD *                                                
 DATA TEMP;                                                   
 SET SASFILE.SELCSEG;                                         
 RUN;                                                         
                                                              
 PROC CPORT DATA=TEMP                                         
            FILE=OUTPUT1;                                     
RUN;    

 

The mainframe code appears to work - no errors, and correct record count written to OUTPUT1

                                                                        

Next, I go to SAS EG (Linux platform), and am running this code:

 

%ftp_remote (ftp -v  &prj_rmt_host << cmd,binary,
lcd /mydir/,
get 'MYID.TEST0516.OUTFILE3' cport.dat,
cmd);

 

LIBNAME mylib "/mydir";
filename convert '/mydir/cport.dat';


proc cimport data=mylib.converted infile=convert;
run;

 

 

But this does not work.  Does anyone have any ideas?

Super User
Posts: 6,543

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

Just take it one step at a time.  We can't really see what is in %FTP_REMOTE, but you would need to check the results of that process to see if it is correct.  I believe that "binary" is suspicious.  SAS transport files are really text and probably don't use a binary transfer method, but I'm not certain on that point.

 

To check the results of the transfer process, you might use:

 

data _null_;

infile convert;

input;

list;

if _n_ = 10 then stop;

run;

Respected Advisor
Posts: 4,557

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

@saslover15

It's now well over a decade ago that I've interfaced with a Mainframe using SAS but what used to work for me is remote library services (you need SAS/Connect on the source and target SAS Server for this).

With remote library services you'd be able to transfer the data directly in a single job from one host to the other.

 

For how you are doing things:

WHAT is not working exactly?

One thing which comes to mind: I don't see any place where EBCDIC to ASCII conversion is happening. May be the Proc Cport TRANSLATION option could help here.

 

Super User
Super User
Posts: 7,860

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

[ Edited ]
Posted in reply to saslover15

The first thing you did wrong was paste code into the forum editor without using the Insert Code or Insert SAS code button. So it is really hard to read your program statements since the forum editor converted them into paragraphs of text.

 

There should be no need to have the extra data step. Just CPORT directly from the original dataset.

Can you really FTP from a tape file?

You must move the file as binary. There should be IBM mainframe specific FTP options you can use to tell FTP that your cport file should be treated as fixed length 80 byte records.  You might also need to add that into the JCL for when you create the file.

 

The files might look like text but the format would be corrupted if you tried to translate EBCDIC to ASCII or insert line breaks. 

 

//SASFILE DD DSN=MYID.SASDATA.TAPE, DISP=SHR
//OUTPUT1 DD DSN=MYID.TEST0516.OUTFILE3,                      
//  DISP=(NEW,CATLG),                                         
//  UNIT=TAPE90,LABEL=RETPD=60                                
//* UNIT=SYSDA,SPACE=(CYL,(20,20),RLSE),MGMTCLAS=MCRET60      
//SYSIN   DD *                                                
                                                              
PROC CPORT DATA=SASFILE.SELCSEG                                      
        FILE=OUTPUT1
;                                     
RUN;    

To see if the file transfer is correct try reading the beginning with SAS data step and see if it looks the same on both systems. Something like this:

data _null_;
  infile xx lrecl=80 recfm=f obs=10 ;
  input;
  list;
run;

If I make a CPORT copy of SASHELP.CLASS on PC-SAS and read it back using that data _null_ the first few lines look like this:

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
1         **COMPRESSED** **COMPRESSED** **COMPRESSED** **COMPRESSED** **COMPRESSED********

2   CHAR  LIB CONTROL X64_8PRO¼. SAS9.4¼ƒI¼¥D901SASHELP CLASS¼™VC5¼‚RC19¼ˆRL40¼ˆ1¼‡S 0  0
    ZONE  44424445544253353554B02545323B84BA43335454445244455B9543B85433B85433B83B85232232
    NUMR  C9203FE42FC0864F802FC603139E4C39C5490131385C003C133C9635C22319C82C40C81C73000000
3          12 NO¼„0¼‰1¼•ansi¼Š¼Î¼ÎStudent Data¼ÂSPAN CONTROL -7¼‚40¼…1¼†40¼ŠL¼ˆ40¼Ž¼&¼¦SPA

4   CHAR  N CONTROL -1¼‚118¼„2¼†38¼ŠL¼‡118¼ŽNAME¼ .À¼..¼..¼.......¼..Name¼....¼. ..¼.¼¨SPA
    ZONE  424445544223B8333B83B833B84B8333B84444B20CB01B00B0000000B004666B0000B1200B0BA554
    NUMR  E03FE42FC0D1C2118C42C638CACC7118CEE1D5C010C18C18C1804002CC2E1D5C1108C5002C8C8301

You should see similar things for your file.  The first 80 characters should be exactly the same.

 

 

Occasional Contributor
Posts: 5

Re: How to read a SAS dataset stored in a txt file in binary format into sas7bdat format in SAS EG?

Posted in reply to saslover15

My apologies, I made a mistake - the code I posted does, indeed, work. 

I went back in to run the code Astounding suggested, and saw it actually worked. 

 

And the results of Astounding's code did look as Tom expected: 

 

data _null_;
infile convert;
input;
list;
if _n_ = 10 then stop;
run;

 

So here is the final solution to getting a Mainframe SAS dataset that resides on tape dataset out of mainframe and into SAS EG (FTP procedure not given intentionally):

 

Run this code in Mainframe:

//SASFILE DD DSN=MYID.SASDATA.TAPE, DISP=SHR
//OUTPUT1 DD DSN=MYID.TEST0516.OUTFILE3,                      
//  DISP=(NEW,CATLG),                                         
//  UNIT=TAPE90,LABEL=RETPD=60                                
//* UNIT=SYSDA,SPACE=(CYL,(20,20),RLSE),MGMTCLAS=MCRET60      
//SYSIN   DD *                                                
 DATA TEMP;                                                   
 SET SASFILE.SELCSEG;                                         
 RUN;                                                         
                                                              
 PROC CPORT DATA=TEMP                                         
            FILE=OUTPUT1;                                     
RUN;    

 

 

 

 

Then run this code in SAS EG:

%ftp_remote (ftp -v  &prj_rmt_host << cmd,binary,
lcd /mydir/,
get 'MYID.TEST0516.OUTFILE3' cport.dat,
cmd);

 

LIBNAME mylib "/mydir";
filename convert '/mydir/cport.dat';


proc cimport data=mylib.converted infile=convert;
run;

 

 

 

Note, BINARY IS REQUIRED in the FTP for this to work.  ASCII will not work since this is coming from mainframe.

 

Thank you so much, all of you, for your assistance. I hope this post helps someone else!

 

 

 

 

 

 

test

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 187 views
  • 1 like
  • 4 in conversation