Using sas dataset in datastage job

bjames · Post by **bjames** » Fri Sep 14, 2012 10:24 am

Hi,
I have a requirement to read a sas file of the format *.sas7badt and load to an oracle table. The file is available in the ETL server itself(FTPed from the SAS server).
We are using datastage8.5 on Linux.

Can someone please explain me
1. what are the prerequisites to use SAS stage in the job
2.How to read the sas file in the job(What details needs to be mentioned in the SAS stage)

Thanks in advance

Bijo

rameshrr3 · Post by **rameshrr3** » Fri Sep 14, 2012 11:17 am

You will find this info the parallel job developer guide and connectivity guide to SAS. Also in 8.5 or 8.7 there is also a legacy SAS dataset stage.

HTH

bjames · Post by **bjames** » Sun Sep 16, 2012 10:42 pm

Hi, I used the below code in SAS stage

sas -source 'libname /apps/dtstgdev/datafiles/cpm/SAS_Test/acct_r.sas7bdat';
DATA liborch.out_data;
RUN;
-output 0 out_data
-schemaFile 'sas_schema'
-workingdirectory /apps/dtstgdev/datafiles/cpm/SAS_Test

The error was like this
main_program: Did not have arguments to generate sasout operator correctly on output 0 of APT_FileExportOperator.
A schema must be specified to the -schema option or the -schemaFile option.

Please correct me i went wrong somewhere

Thanks
Bijo

vmcburney · Post by **vmcburney** » Sun Sep 16, 2012 11:55 pm

It's my understanding that in order to use the DataStage SAS stages you need to have SAS software and a SAS license on the DataStage server. Does anyone know if you can open a SAS dataset without a SAS engine present?

bjames · Post by **bjames** » Mon Sep 17, 2012 12:12 am

Any help on this would be highly appreciated

Thanks
Bijo

bjames · Post by **bjames** » Mon Sep 17, 2012 12:24 am

I have read in one of the IBM documents that, we should change the configuration file to add the location of the SAS executables. I am not able to identify whether SAS executables are already installed in server or not...
Can someone please tell me is there a way to identify this

Thanks

vmcburney · Post by **vmcburney** » Mon Sep 17, 2012 12:34 am

This is some help I got from IBM a few weeks ago on this same issue:

We interface with SAS by actually executing SAS under the covers. In other words, you need to have SAS installed on the same box because we execute the sas command. To read and write SAS data, we run sas to execute a SAS DATA step. Our SAS stage also can run as PROC execution commands. Our benefit is that we can run the SAS stage in parallel. So we partition the data coming into the SAS stage, and then run the PROC step in parallel. Also, once we have read the data from a sequential sas dataset via a DATA step, we can also read and write to parallel sas datasets which interface nicely with running the PROC steps in parallel. Our strength with integration with SAS is that we can generally parallelize the overall SAS application (obviously depends on application logic, etc) as easily as we can parallelize any other Datastage job.

So a common data flow, along those lines is,
SAS stage (DATA step to read sas) -> partition the data -> SAS stage (parallel PROC step) -> parallel SAS dataset
parallel SAS dataset -> parallel SAS proc step -> parallel SAS dataset
and so on...

bjames · Post by **bjames** » Mon Sep 17, 2012 1:16 am

My doubt here is that we normally install oracle drivers and client in the server to connect to oracle DB. Is this scenario holds the same for SAS connection too.

While we install the SAS stages, will the required executables automatically get installed or do we need to purachase any license and install them later

Please let me know

Thanks

bjames · Post by **bjames** » Mon Sep 17, 2012 6:10 am

Requesting all DS gurus to help on this

Thanks

chulett · Post by **chulett** » Mon Sep 17, 2012 7:06 am

bjames wrote:While we install the SAS stages, will the required executables automatically get installed or do we need to purachase any license and install them later

For the two questions wrapped around the "or" - no, yes. With the SAS stages in DataStage, just like any other add-on you get the ability to interface with SAS, you don't get SAS itself.

And don't push, people help when and if they can and occassionally sleep as well.

jwiles · Post by **jwiles** » Mon Sep 17, 2012 7:19 am

To determine whether or not SAS is installed on your servers, ask your system administrators or look through SAS installation documentation (should be available from SAS) for assistance.

Regards,

bjames · Post by **bjames** » Mon Sep 17, 2012 8:19 am

I have confirmed with the Admin that we dont have SAS installed in the ETL server.
Would there be any other option to read the file of the form *.sas7bdat. Kindly let me know

jwiles · Post by **jwiles** » Mon Sep 17, 2012 9:00 am

IIRC, SAS datasets are a proprietary format which require that you use SAS software to read (through the SAS stage in this case).

You may need to request that the supplier export the data as a regular text file if you are unable to have the appropriate SAS software installed on the server. If there is another team/group within your client's organization that regularly receives data from this supplier in a SAS dataset, perhaps they may be of assistance.

Regards,

lstsaur · Post by **lstsaur** » Mon Sep 17, 2012 5:03 pm

No other way that you can run the SAS dataset without the licenses installed on nodes for SAS. Besides it's just too expensive!
What are you trying to accomplish? From reading your SAS code, it seems like it's not worth it.