f



Re: Loading SAS Data Set To Oracle Error #3

Toby,

I'm not sure what is happening under the hood on the Oracle side when
you're doing the proc append, but if it involves PL/SQL, your access via
the DBA role is insufficient.  PL/SQL requires explicit CREATE TABLE
privileges in the schema, not just privileges granted through a role.


Carol Bristow
DPRA Inc.
1655 N. Ft Myer Dr  Ste 925
Arlington, VA 22209
phone: 703-682-2612
fax: 703-528-3100


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
toby dunn
Sent: Wednesday, November 14, 2007 5:38 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Loading SAS Data Set To Oracle Error

Im trying to load a SAS data set to our Oracle DataBase using various
methods the only method I have found that works is Proc DBLoad which has
a limitation to the lengths of variable names of 8 characters, Im not
about to go renaming 100 plus variables.

The error we keep getting is:

74   Proc Append
75     Base = ORA.Test2
76     Data = Test ;
77   Run ;

NOTE: Appending WORK.TEST to ORA.TEST2.
NOTE: BASE data set does not exist. DATA file is being copied to BASE
file.
ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute
error:
       ORA-01031: insufficient privileges..


Any clue as to what the heck privileges I need.  We have added me on as
a DBA role and that doesnt even seem to fix the problem.

Toby Dunn

Compromise is like telling a lie, it gets easier and easier. Each
compromise you make, that becomes your standard.

Perfection doesnt exist, once you reach it, its not perfect anymore. It
means something else.

_________________________________________________________________
Peek-a-boo FREE Tricks & Treats for You!
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us
0
11/15/2007 4:46:58 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
719 Views

Similar Articles

[PageSpeed] 55

Reply:

Similar Artilces:

Re: How to filter sas data sets into separate sas data sets #3
Lizette, Instead of trying to merge the two data sets, I would probably try to create a SAS format from the values of VAR1 in data set 1. Then, NODE1, NODE2 and NODE3 could be compared against the format for a match. The example below is a simplified version of what you could do and shows a printout of how it works. It has 5 observations in data set 1 and only 3 variables in data set 2, but I think the logic should hold for the example you gave. After the example is code that could be used to actually split the data as you had requested. Hope this helps. * create sas data set 1 ; data ...

Re: Loading SAS Data Set To Oracle Error
You definitely can move sas data to databases in this way--I do it all the time. You're right that you can't overwrite a table that exists in the database, but you can create new tables. -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Gerhard Hellriegel Sent: Thursday, November 15, 2007 9:33 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Loading SAS Data Set To Oracle Error I don't think, that you can use standard SAS to bring data to ORACLE or any other relational DB. The ORACLE tables look like SAS tables if you are using the ACCESS-libname, but they aren't. E.g. you cannot replace datasets by simply use data ORA.test; set sashelp.class; run; That might work the first time for a temporary table. But the second trial tells you something like an SQL error. For a ORACLE table I don't think that it is that simple. You must first define that table and then INSERT records with pass-through. So I think, you can use DBLOAD to load a entire table. If you want to add records, you can use SQL - pass-through with INSERTs. I don't think PROC APPEND will do that. Gerhard On Thu, 15 Nov 2007 10:46:58 -0600, Carol Bristow <Carol.Bristow@DPRA.COM> wrote: >Toby, > >I'm not sure what is happening under the hood on the Oracle side when >you're doing the proc append, but if it involves PL/SQL, your access >via the DBA role is insufficient. PL/SQL requires explicit CREATE >TABLE privi...

Re: Loading SAS Data Set To Oracle Error #2
Roy et. all, Well problem is solved it seems the DBA had my schema and table space permissions screwed. we have managed to get those fixed so im back in business. Toby Dunn Compromise is like telling a lie, it gets easier and easier. Each compromise you make, that becomes your standard. Perfection doesnt exist, once you reach it, its not perfect anymore. It means something else. > Subject: RE: Loading SAS Data Set To Oracle Error > Date: Thu, 15 Nov 2007 08:09:07 -0800 > From: pardee.r@ghc.org > To: tobydunn@HOTMAIL.COM > > I don't suppose it could be something as simple as not specifying a > default schema on your libname statement, could it? > > Can you do CREATE TABLE and/or INSERT INTO in proc sql? (Not suggesting > that as a workaround--just a diagnostic.) > > You're using what--sas/access for oracle? Or one of the other > sas/accesses? > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > toby dunn > Sent: Wednesday, November 14, 2007 2:38 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Loading SAS Data Set To Oracle Error > > Im trying to load a SAS data set to our Oracle DataBase using various > methods the only method I have found that works is Proc DBLoad which has > a limitation to the lengths of variable names of 8 characters, Im not > about to go renaming 100 plus variables. > > The error we keep getting is: > > 74 Pro...

Re: Loading SAS Data Set To Oracle Error #4
I don't think, that you can use standard SAS to bring data to ORACLE or any other relational DB. The ORACLE tables look like SAS tables if you are using the ACCESS-libname, but they aren't. E.g. you cannot replace datasets by simply use data ORA.test; set sashelp.class; run; That might work the first time for a temporary table. But the second trial tells you something like an SQL error. For a ORACLE table I don't think that it is that simple. You must first define that table and then INSERT records with pass-through. So I think, you can use DBLOAD to load a entire table. If you want to add records, you can use SQL - pass-through with INSERTs. I don't think PROC APPEND will do that. Gerhard On Thu, 15 Nov 2007 10:46:58 -0600, Carol Bristow <Carol.Bristow@DPRA.COM> wrote: >Toby, > >I'm not sure what is happening under the hood on the Oracle side when >you're doing the proc append, but if it involves PL/SQL, your access via >the DBA role is insufficient. PL/SQL requires explicit CREATE TABLE >privileges in the schema, not just privileges granted through a role. > > >Carol Bristow >DPRA Inc. >1655 N. Ft Myer Dr Ste 925 >Arlington, VA 22209 >phone: 703-682-2612 >fax: 703-528-3100 > > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >toby dunn >Sent: Wednesday, November 14, 2007 5:38 PM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Load...

Re: How to filter sas data sets into separate sas data sets #4
Something like this is the old way. You could use a proc sql if you have a new enough version. Increase your buffersize and if you have enough memory you may get it into a hash routine. DATA WORK.NEW; MERGE small (IN=A OBS=500) big ; BY ID_FIELD; IF A=1; RUN; QUIT; RICH -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@listserv.vt.edu] On Behalf Of Lizette Koehler Sent: Monday, April 02, 2007 10:53 AM To: SAS-L@LISTSERV.VT.EDU Subject: How to filter sas data sets into separate sas data sets Listers, This is my failing point in coding SAS. T...

Re: Reading & Writing SAS data sets without SAS #3
Chang, You're correct in that a number of companies have done it. I believe SPSS can do it, WPS, Stat Transfer, dbmscopy, and perhaps others have also done it. But what I think is unique about this is that Alan is talking about offering plug-ins so you can roll-your-own so to speak. How cool would it be to have some type of driver/plugin for R? Phil Philip Rack MineQuest, LLC SAS & WPS Consulting and WPS Reseller Tel: (614) 457-3714 Web: www.MineQuest.com Blog: www.MineQuest.com/WordPress -----Original Message----- From: Chang Chung [mailto:chang_y_chung@HOTMAIL.COM] Sent: Monday...

Re: Exporting a SAS data set to Text file on SAS unix #3
hi ... actually, what I posted earlier was too much code (sorry) this is enough (a bit more succinct) * variable names into a macro variable (tab separated); proc sql noprint; select name into :vars separated by '09'x from dictionary.columns where libname eq 'SASHELP' and memname eq 'CLASS' order varnum; quit; data _null_; file 'z:\class.txt' dsd dlm='09'x ; if _n_ eq 1 then put "&vars"; set sashelp.class; put (_all_) (:); run; -- Mike Zdeb U@Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402...

Re: Load time , sas data-set to Sql Server #3 1564108
auto208611@HUSHMAIL.COM wrote: > >I'm creating a Sql Server 2000 table from a SAS data set and it's >taking a much longer time then expected. > >I've tested loading a small data-set approx. 23040 rows and 11 >variables/columns which took about a minute to complete. > >libname sqlserv oledb init_string="Provider=SQLOLEDB.1; > Integrated Security=SSPI; > Persist Security Info=True; > Initial Catalog=myCatalogName; > ...

Re: How to filter sas data sets into separate sas data s ets
I think that both Ron's (as he mentioned) and Richard solutions require that VAR1 is in both datasets. But from the original post, it seemed to me that VAR1 is only in data set 1, and it must be matched to 1 of 3 variables in data set 2 (NODE1, NODE2 or NODE3) to be output to the NEWLIST data set. For this reason, I think a format is one possible approach. Maybe the original poster can clarify this point. Thanks. Jack Clark Research Analyst Center for Health Program Development and Management University of Maryland, Baltimore County -----Original Message----- From: SAS(r) Discussio...

Re: XML data to SAS data set converstion #3
Since a SAS dataset is not much good without SAS - unless your intent is to read it using something like StatTransfer, one would assume that the eventual target is for the data to be input into SAS. There are a couple of other possibilities. It would be possible to write a script in the language of your choice to pull the data from the XML file and also write data step code to read it. Another possibility would be to write an XMLMap file (another XML file) to describe how the transformation from the XML data file into one or more datasets is to be done. Since the XMLMap file is just another XML file it could be generated by an XML editor, a text editor, or the appropriate script. The SAS XML engine can use this XMLMAP file to read the original XML data file as if it were a SAS library. The XMLMAP file only needs to be created once to describe the transformation from a particular flavor of XML file (having a particular schema or DTD) to a particular set of SAS datasets. A single XML file can represent a collection of data that could be awkward to use in a single table, as in the case of a whole collection of tables from a relational database dumped into one XML file. While you could represent the XML file as a table with a column for the XPATH and a column for the content of that element or attribute, that would not necessarily be the best representation for working with the data. Instead, an XMLMAP file can direct the SAS XML engine to create a collection of tables. L...

Re: search SAS data set from SAS code
> From: Rose > Hi All, > Suppose I have a sas permanent data set which was created > early, I know > the library path but I couldn't remember in which sas program code I > created it. how can I search from so many sas program files in > different folders and find it. a problem familiar to all of us delayed-housekeeping folks. Libname Libref '<directory-specification>'; DATA LibRef.DataSetName; use your system utilities to search for the dir-spec of your libref. search: *.sas containing text: <dir-spec> once you have found the libname...

Re: Deleting SAS Data from a SAS DATASET #3
The disadvantages of using SQL delete are (1) NOBS= is no longer accurate, and (2) POINT= may behave unexpectedly. If those are not issues, then the delete would be faster, with or without an index, than recreating the data set (especially if the data set has indexes that would need to be rebuilt). -- Jack Hamilton jfh@alumni.stanford.org On Aug 15, 2008, at 11:17 am, Mary wrote: > One thing you might do is to add an index on the snap_dt to the > dataset; if that's there then you should be able to delete the > records in place: > > proc sql; > delete from prod.master_date; > where snap_dt = "&end_dt"d; > quit; > run; > > In both the ways you are trying now you are creating new data sets > rather than deleting records from the current data set; it would > seem to me that a SQL delete statement would be faster than creating > new datasets even if there isn't an index on the date. > > -Mary > ----- Original Message ----- > From: SUBSCRIBE SAS-L Chandra Gadde > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 12:14 PM > Subject: Deleting SAS Data from a SAS DATASET > > > Hi All > > I have several SAS datasets that are very very big. (50GB of size). > Every > month, the data is being appended to these datasets. I need to > deleted the > data which is greater than 24 months. What is the best method to do > this? > Please help me. > ...

Re: Numeric precision was >> Re: Oracle to SAS
I don't understand how creating new formats would help me get around this. Can you explain (or give an example)? Thanks! Steve On Fri, 24 Sep 2004 11:40:53 -0600, Jack Hamilton <JackHamilton@FIRSTHEALTH.COM> wrote: >I don't know of any way around the problem. That's why there are >requests for an integer, fixed-point, or currency numeric type in SAS in >addition to the floating point numbers now available. > >If you're only adding and subtracting, calculating in pennies isn't too >difficult - you just have to create new formats to display decimal...

Re: search SAS data set from SAS code #2
Rose, The answer to your question depends on your operating system. In Windows, there's the Search tool. In Unix/Linux, you can use grep Bob Abelson HGSI 240 314 4400 x1374 bob_abelson@hgsi.com Rose <myr_rose@YAHOO.COM> Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> 04/19/2005 11:13 AM Please respond to myr_rose To: SAS-L@LISTSERV.UGA.EDU cc: Subject: search SAS data set from SAS code Hi All, Suppose I have a sas permanent data set which was created early, I know the library path but I couldn't remember in which s...

Re: search SAS data set from SAS code #5
Rose, You have some good advice on search techniques, but they may beinadequate. I hope your LIBNAME wasn't something like libname lib "&dir" ; Perhaps you should also search for ".member", but that also couldhave the same problem. You might also look for key variablenames or values, or procedures that you know created the data.The date from a PROC CONTENTs might provide useful information,or an old report created by the same program with a footnote,"Source code: ...". Maybe data lib.w ( label="created by ..." ) ; would be a good habit to ...

Re: sas data sets to excel #3
Maybe talk your client out of this? Or just do "passive-aggressive" and = send it on two sheets? It is a really, really bad idea to put two = different data sets on the same sheet.=20 -Mary ----- Original Message -----=20 From: Qiang Fu=20 To: Mary=20 Sent: Wednesday, October 15, 2008 10:08 AM Subject: Re: sas data sets to excel Hi, Thanks for your quick reply. I think, however, I didn't express me = clearly in the MSG. What I want is to export two different SAS data sets into ONE = weeksheet with one tab name instead of two weeksheets with two different = tab names. The example you gave will export the data sets into two = different tabs with names "sheet1" and "sheet2" respectively even they = are in same workbook. I know this task sounds insane, but client needs. = Any idea? Thanks a lot. Qiang On Wed, Oct 15, 2008 at 10:24 AM, Mary <mlhoward@avalon.net> wrote: Hi, I like to use Excel Tagsets for this; it will actually write an XML = file but you can open an XML file in either Excel 2003 or 2007. Setting = the sheet name switches sheets. Here is an example: Excel Tagsets give you a lot of formatting control; here is an = example: ods listing close; ods tagsets.excelxp file=3D"c:\temp\file1.xml' style=3Danalysis = options(absolute_column_width=3D'10,30,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,= 8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8' sheet_label=3D' '...

Re: SAS/ACCESS
Does the DBMAX_TEXT option do what you want? http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a003113591.htm -- Jack Hamilton jfh@alumni.stanford.org Videtis illam spirare libertatis auram On Jan 22, 2009, at 8:57 am, Matthew Pettis wrote: > Hi, > > > > I have a CLOB coming back from an Oracle passthrough query that is > >1024 > in length. How do I specify that my receiving variable in a SAS > dataset > be longer than 1024 characters to accommodate this? By default, I get > SAS thinking that this is 1024 characters, when it is really a CLOB > and > I want to set some large default length on this variable length... > > > > Thanks, > Matt ...

Re: SAS 9.1.3, Oracle 10G And Oracle RAC #3
Good luck with resolving the issue. It sounds like it is probably more of an Oracle issue in that case. Have you contacted Oracle? Sorry I can't be of more help for RAC tuning, Rgds. ...

Re: Reading SAS data sets on UNIX by non-SAS apps #2
John: Following on Richard's thoughtful suggestions, the Affinium system would likely capture data from csv files. SAS PROC EXPORT produces them quickly, and loading them into external systems works faster for relatively basic data structures and data formats, in my experience, than xml parsing. Sig -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of John Bentley Sent: Monday, October 18, 2004 10:10 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Reading SAS data sets on UNIX by non-SAS apps I have SAS data sets on AIX that we need to read with Unica's Affinium campaign management software, also on a UNIX box. (Let's not get into why we didn't go with the SAS Solution.) SAS Institute doesn't supply an ODBC driver for the UNIX environment, and the Affinium implementors don't want to use the SAS SQL Library for C and or deal with APIs. Other that dumping the SAS data sets as flat files, can anyone suggest a solution? Thanks in advance for the help. ...

Re: SAS/ACCESS
DBMAX_TEXT does the trick; thank you all who helped! Matt -----Original Message----- From: Jack Hamilton [mailto:jfh@stanfordalumni.org] Sent: Sat 1/24/2009 1:42 AM To: Pettis, Matthew (Prof II&RS) Cc: SAS-L@LISTSERV.UGA.EDU Subject: Re: [SAS-L] SAS/ACCESS - Oracle: setting field lengths in SAS datasets returned from passthrough Does the DBMAX_TEXT option do what you want? http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a003113591.htm -- Jack Hamilton jfh@alumni.stanford.org Videtis illam spirare libertatis auram On Jan 22, 2009, at 8:57 am, Matthew Pettis wrote: > Hi, > > > > I have a CLOB coming back from an Oracle passthrough query that is > >1024 > in length. How do I specify that my receiving variable in a SAS > dataset > be longer than 1024 characters to accommodate this? By default, I get > SAS thinking that this is 1024 characters, when it is really a CLOB > and > I want to set some large default length on this variable length... > > > > Thanks, > Matt ...

Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar > Sent: Tuesday, July 21, 2009 8:10 AM > To: sas-l@uga.edu > Subject: What r the data types in SAS? in Base SAS , and SAS SQL > > Hi all, > What are the different data types in Base SAS and SAS SQL?? character, lengths from 1 to 32,000+ numeric: lengths from 2 to 8 dates are a subtype of numeric and are identified by their date, datetime, or time formats the new proc TSPL (Table Server Processing Language) supports ANSI data types: bigint, tinyint, etc. http://support.sas.com/documentation/cdl/en/tsag/30878/HTML/default/a003 065339.htm http://s...

Re: SAS data-set index size #3
auto208611@HUSHMAIL.COM wrote: > >Is a telling story if the size of the SAS index file (*.sas7bdnx) is >75% the size >of the SAS data-set itself? > >For instance, we have a data-set that is 533MB's and the index file >is >400MB's. > >Is this an indication of a poor data-set structure? No, it is not an indication of a poor data structure. (You may *have* a poor data structure, but the relative size of the index file is not indicative.) Since Mister Index himself has already chipped in, I'll just add a couple other points. Think conceptually of the i...

Re: SAS with data sets of a billion+ records #3
Dear SAS-L-ers, Richard Reeves posted the following: > I am working with some tables that have between 600 million > and 1.2 billion records. In the end I will only need about > 400 million of these records but to identify them I will have > join several tables together (sorting or indexing each time) > to get to this point. The files don't have a lot of fields as > they come from a very normalized transactional system so even > when everything is joined there will be <50 fields. > > Before I get too far down this road I would like to know if > anyone re...

Re: how to find sas sample data set #3
Thank all for your answers! It works now. Orange On Tue, Feb 24, 2009 at 5:22 AM, Gerhard Hellriegel < gerhard.hellriegel@t-online.de> wrote: > Something with "sample" if they are installed... > It depends on the SAS module. For BASE it is ...\core\sample\ > Gerhard > > > On Mon, 23 Feb 2009 23:22:48 -0500, help ly <help.ly2005@GMAIL.COM> wrote: > > >Hi, I am trying to find SAS sample data set such as medical data set or > >hospital data set because it has to be used in those excersize problems in > >the sas books - Learning SAS by Example. There are supposed to be many > of > >those sample data set from SAS itself, right? > > > >Can anyone help me what's the directory for the data set? > > > >Thanks so much! > > > >Orange > >help.ly2005@gmail.com > -- Thanks so much! Orange help.ly2005@gmail.com Hi Orange, I am facing the same problem you had before. How did you find the example data? I don't see them in either SASHELP or SASUSER library. Thank you! Gavin On Tuesday, February 24, 2009 1:29:11 PM UTC-5, help ly wrote: > Thank all for your answers! It works now. > > Orange > > On Tue, Feb 24, 2009 at 5:22 AM, Gerhard Hellriegel < > gerhard.hellriegel@t-online.de> wrote: > > > Something with "sample" if they are installed... > > It depends on the SAS module. For BASE it is ...

Web resources about - Re: Loading SAS Data Set To Oracle Error #3 - comp.soft-sys.sas

Loading screen - Wikipedia, the free encyclopedia
A loading screen is a picture shown by a computer program , often a video game , while the program is loading or initializing. In early video ...

Facebook Adds Mobile Reach Data To Insights, Cuts Back Loading Of Organic Stories
Facebook announced two changes to its insights Tuesday: the inclusion of mobile reach data, and the lowering of the number of organic stories ...

Facebook’s Fast-Loading and Free “0″ Mobile Site Launches
More than a quarter of Facebook’s users are accessing it through mobile devices every month, and the number of mobile useres is growing fast. ...

Search Twitter - witcher 3 loading
... are using an old version of Internet Explorer. Learn more here Search Refresh WCCFtech @ wccftechdotcom 31m The Witcher 3 Experiencing Loading ...

Gmail : Why does "?shva=1" get appended to the URL when loading Gmail?
Answer: "shva" is an acronym for "should have valid authentication"

Loading a Bradley - Flickr - Photo Sharing!
Pfc. Joseph Salerna, 2nd Battalion, 69th Armor Regiment, 3rd Heavy Brigade Combat Team, 3rd Infantry Division, backs his Bradley Fighting Vehicle ...

Dishwasher Loading - Teenage Instructional Video #2 - YouTube
Facebook: https://www.facebook.com/willreid.co Website: http://www.willreid.co Twitter: https://www.twitter.com/willreidco Well here it is! The ...

End to leave loading will recoup 'small beer'
End to leave loading will recoup 'small beer'

Penalty rates: Loadings for working weekends and holidays could be over
This could be the last Christmas millions of Australian workers will claim penalty rates for working on weekends and public holidays, unions ...

Google announces service to speed loading of news articles
Google shows off initiative to ensure news articles load more quickly on the web, but one name was conspicuously absent from the presentation. ...

Resources last updated: 3/17/2016 4:34:32 PM