[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

sas unix

I have a sas file file.sas7bdat , which is nothing but a backup of my table in oracle. Now I want to load that file into a new table , for testing,  How do I do that using sas on unix
0
Sara_j_11
Asked:
Sara_j_11
  • 3
  • 3
1 Solution
 
Sara_j_11Author Commented:
I tried doing this:
nm = '/m/mydir';
 proc export data=nm.file outfile = '/mer/test.d
one'
 dbms=dlm replace; delimiter= '|'
run;


But it takes a long time and it looks like it gets hanged.
0
 
Aloysius LowCommented:
hi sara_j_11,

you want to write data from sas dataset back into oracle? is it the same database as where you have the dataset exported from?

what i'm thinking is to do a simple libname statement to the oracle database and then do a data step to put the data back into oracle from sas... not sure if that's what you want though, of if you have tried it already.
0
 
Sara_j_11Author Commented:
Not sure if I understand --- a simple libname statement to the oracle database . Can u explain with example?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Aloysius LowCommented:
It would look something like:
LIBNAME [your library name] ORACLE  PATH=[as defined in tnsnames]  SCHEMA=[as provided by DBA - the schema to access] USER=[username for database] PASSWORD=[password for username provided]

Haven't touched this for a very long time so not too sure if this will work especially the options PATH and SCHEMA. I only recall one of them has to be as defined in tnsnames.ora file.

How do you currently/previously connect to your Oracle database when you export the tables out into SAS dataset?

P.S. Just to make sure I have the correct understanding - previously you have a table in Oracle which you have made a backup of as a SAS dataset, and now, you want to put the data in the SAS dataset back into Oracle, but using a different table name? Is this correct?

If yes, is this the same machine/server you are using? If it's a different machine, you might need to set up Oracle client, I believe. It's been quite a long time since I last did something like this, so trying my best to help out here :)
0
 
Sara_j_11Author Commented:
P.S. Just to make sure I have the correct understanding - previously you have a table in Oracle which you have made a backup of as a SAS dataset, and now, you want to put the data in the SAS dataset back into Oracle, but using a different table name? Is this correct?


MY ANSWER:::: YES

If yes, is this the same machine/server you are using?

MY ANSWER:::: YES



0
 
Aloysius LowCommented:
have you tried the libname statement method? does it work?

how did you make the backup the last time?
0
 
bradanelsonCommented:
Sounds like you already have the pathname setup to connect to ORACLE.  Here is an example of what you would do to connect and load a table.  I have set the username, password and pathname as global variables.  You could either create the global varilables above or hardcode this info.  Also, be sure you delete the table before trying to create, otherwise you will see an error.

Let me know how it goes.

Thanks


LIBNAME nm = '/m/mydir';

PROC SQL;
    CONNECT TO ORACLE (USER="&Username" PASS="&Password" PATH="&Pathname");

    DROP TABLE [ORACLE SCHEMA].[ORACLE TABLE NAME];
   
    CREATE TABLE [ORACLE SCHEMA].[ORACLE TABLE NAME] AS
    SELECT *
    FROM nm.file;

    DISCONNECT FROM ORACLE;
QUIT;
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now