BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dash
Obsidian | Level 7

Hello Everyone!!!

I am new to this forum. I want to create one table in oracle from SAS and then import 1.5 lakh records from excel. How is it possible. Can anyone help me

Can I use import/export procedure. Please help me

I tried using another approach but its not working.

proc sql;

        connect to oracle as EXT1 (user=xx orapw=xxx path=xxxx);

            create table temp as

                Select * from connection to EXT1

                                (SELECT  num_id from master where num_id in (1.5 recordsss..............)');

quit;

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

This is the error you get if SAS/ACCESS for PC Files is either not installed or licensed.

Run: PROC SETINIT NOALIAS; RUN; to confirm if you have this product licenced or not.

If not licensed then you can save the Excel spreadsheet as a CSV file then:

PROC IMPORT OUT = my_spreadsheet

        FILE = "c:\my_spreadsheet.csv"

        DBMS = CSV

        REPLACE

        ;

RUN;

You can then follow Tom's solution.

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

Why not just use LIBNAME statements so that you can treat the Oracle database and Excel file the same as other SAS datasets?

libname out oracle user=xx orapw=yyy path=zzz schema=aaa ;

libname in excel 'myfile.xls';

data out.want ;

  set in.have;

run;

You might need to play around a little to find the name of the sheet in the excel file that you want to read.

In SAS there does not need to be a separate step to "create" a table before writing data into it.  SAS will create the table as it writes it.  The same way as when you "Save as" in Word.

dash
Obsidian | Level 7

Thanks Tom, But I am getting error. I am totally new to SAS. Could you please explain me in details.

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

libname out oracle user=scott orapw=tiger path=orcl ;

libname in excel 'N:\abc\asd.xls';

data out.want ;

  set in.have;

run;

SASKiwi
PROC Star

This is the error you get if SAS/ACCESS for PC Files is either not installed or licensed.

Run: PROC SETINIT NOALIAS; RUN; to confirm if you have this product licenced or not.

If not licensed then you can save the Excel spreadsheet as a CSV file then:

PROC IMPORT OUT = my_spreadsheet

        FILE = "c:\my_spreadsheet.csv"

        DBMS = CSV

        REPLACE

        ;

RUN;

You can then follow Tom's solution.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 765 views
  • 0 likes
  • 3 in conversation