Im

[dash]

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;

[Tom]

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]

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 · Accepted solution]

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.