LinusH, glad that even though none of you got the "real" question quite right, the problem is understood. Sorting of alpha numeric characters (see title) If there is no ansi sql standard that I can use with my proc sql, and believe me, I looked a lot through Oracle documentation (but one can never be sure to cover it all -so I asked here), then I was hoping there is a SAS one. Various SAS people I have asked in the meantime suggested to fill the single digit numbers (rpad in oracle -but does not work for me *ERROR: Function RPAD could not be located.*) with 0 and try the sorting again. - I think this comes close to what Quentin suggested with  order by input(var,8.) ... View more

I did ask if  someone knows a standard ansi sql way of doing it in oracle (proc sql pass through) since this does not seem to exist. the error I get I posted. I am happy to learn about a way how to do it in sas. what I tried yesterday , after the proc sql. a proc sort. but the proc sort did not work. proc sort data=  t_stp_temp noduprecs; by myIDnumber ; run; ... View more

Reeza. I know that but that does not solve the problem. How do I sort my alpha numeric data in the column. Numbers first, then the letter combinations. ... View more

Helllo Quentin, Thanks for your reply. Unfortunately I can't provide a more extensive example but why is it needed for sorting? Your suggested "order by input(var,8.)" worked! Just that the letter sorting like EA JK QO 1 2 3 comes before the numbers, and I need it the other way around. Yes, my Oracle query looks like yours. proc sql;   connect to oracle (user=... );   select * from connection to oracle     (       select *       from MyFavoriteOracleTable     ); ...   but...Oracle specific functions like DECODE do not work. (ERROR: Function DECODE could not be located.) @LinusH, Thanks for your input. I need to use SQL pass-through. no way around it. ... View more

Hello Quentin. I am using PROC SQL (in a SAS STP) and sql pass through, that is right but the SAS driver's for Oracle only allow me to use ANSI standard sql function not Oracle specific functions like decode or to_date, etc. Is there no ANSI standard SQL I can use for ordering alphanumeric data? Too bad. alphanumeric data, stuff like: 1 3 65 42 EA UD EB @Reeza, PROC SQL, sorting a column that contains alphanumeric data Thanks ... View more

Hello drice0504, I tried your macro but how do I use it in the sql query? My idea was to use the macro variable that is the list and put it in the code like this: PROC sQL... WHERE contractname IN (&LListOfContracts) Thanks for your input. @FriedEgg, thanks for that as well. I have a dynamic list. If I try to put in my prompt name in your example I get the following error: ERROR: Illegal reference to the array prompt_1. NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).       1159:1   NOTE: The SAS System stopped processing this step because of errors. data myNewlist; array prompt_1[&contractsprompt_count] ; do i=1 to dim(prompt_1); prompt_1=symget(cats(vname(prompt_1),i)); end; call symputx('allpromptvalues',catx(',',of prompt_1)); run; ... View more

Hello Doc@ Duke, many thanks for your reply. That leads me to conclude that the "create as View" functionality in the "phyiscal storage" tab of the SAS DataSet properties has acutally no impact on the physical storage because the SAS DataSet, part of the SAS ACCESS/ORACLE library is actually just a "link" to the Oracle Database where the data is phyiscally stored. Does that make sense? ... View more

Hello Patrick, "This would only have an effect if you actually would create the table (and not only read data from it)." So how do I do that? Right now, by default, everything seems to be a "link" to the Oracle database, no data replication, regardless if I tick this box "CREATE VIEW" or not - interesting. "Chosing "create as view" or not only has an effect if SAS needs to create/write to the table." Where is that funcionality? The table is registered in the meta data so how do I say, please SAS "Create" the table, dont just "linK" it? I know about cubes but I dont want to build a cube so we dont have to talk about aggregating cubes or not. "Again: Use SMC and simply register the tables - don't create the metadata manually." -> I dont create it manually. I used OLAP CUBE STUDIO and noticed it has this odd "CREATE AS VIEW" checkbox that does not exist in the SMC. I try to sum it up , so we have 1.table registration in meta data 2.table creation where does "CREATE AS VIEW" come in? ... View more

Linus, I tested it again and the PROC OLAP error is "Metadata registration of table X was not found." How did you do that? ... View more