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;
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.
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.