DATA Step, Macro, Functions and more

Im

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Im

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;


Accepted Solutions
Solution
‎07-10-2012 04:06 PM
Super User
Posts: 3,254

Re: Im

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


All Replies
Super User
Super User
Posts: 7,050

Re: Im

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.

Contributor
Posts: 27

Re: Im

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;

Solution
‎07-10-2012 04:06 PM
Super User
Posts: 3,254

Re: Im

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.

🔒 This topic is solved and locked.

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

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