topic Re: Im in SAS Programming

Im

dash — Tue, 10 Jul 2012 19:14:19 GMT

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;

Re: Im

Tom — Tue, 10 Jul 2012 19:22:28 GMT

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.

Re: Im

dash — Tue, 10 Jul 2012 19:34:30 GMT

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;

Re: Im

SASKiwi — Tue, 10 Jul 2012 20:06:12 GMT

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.