Monotonic Function on Sybase Database

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Monotonic Function on Sybase Database

Hello,

I am trying to use monotonic function in Proc Sql Join.

The join is between multiple tables on Sybase database. I am connecting to sybase through sql passthrough.

I have tried the following options:

1.

,MONOTONIC() AS COUNT

.................

GROUP BY A.VAR_1 ,G.VAR_2

........;QUIT;

ERROR:

ERROR: Error Code: -156 Incorrect syntax near the keyword 'count'.

2.

,ROW_NUMBER () OVER (PARTITION BY A.VAR_1 ,G.var_2 order by A.VAR_1 ,g.VAR_2 ) AS COUNT

ERROR:

ERROR: Error Code: -156 Incorrect syntax near the keyword 'OVER'.

Please help.
Thanks


Accepted Solutions
Solution
‎08-27-2013 07:09 PM
Respected Advisor
Posts: 4,173

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

Monotonic() is an unsupported SAS function. "unsupported" = not to be used for production code, "SAS function" = not available in pass through SQL where you are using db native SQL.

View solution in original post


All Replies
Solution
‎08-27-2013 07:09 PM
Respected Advisor
Posts: 4,173

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

Monotonic() is an unsupported SAS function. "unsupported" = not to be used for production code, "SAS function" = not available in pass through SQL where you are using db native SQL.

Super Contributor
Posts: 307

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

One option is to output the join to a SAS dataset and then use monotonic to number the rows. This is not necessarily the most efficient solution, but it works.

proc sql;

connect to sybase(server=yourserver

  database=yourdb

  user=youruser

  password=yourpass);

create table have as

select * from connection to sybase

  (. .  .your select code . . ..);

quit ;

proc sql;

     create table want as

     select monotonic() as rowid

          , t.*

     from have t

;

quit;

Trusted Advisor
Posts: 3,214

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

SQL was not developed or designed to do analytics. http://en.wikipedia.org/wiki/SQL Although this attempt is often made.

It was designed to support the data management for operational processes.

As of that nature it is very well suited for parallelization, threading and grid. By that it loses any sense of ordering it sometimes had at older processing modes. Even if monotonic() would give something it cannot be trusted, there is some reason of not being supported.

The SAS datastep was designed for sequential processing. By that it behaves different with the disadvantage of no automatic multithreading or grid processing. Tuning and performance must be well designed in the process.

---->-- ja karman --<-----
Contributor
Posts: 38

Re: Monotonic Function on Sybase Database

Thank You everyone for helping me on this.

The reason I am trying to use Monotonic() is because I want to remove duplicates at the same time when I am extracting data.

I am trying to follow this:

http://www.sascommunity.org/wiki/Tips:Nodupkey_using_PROC_SQL

Is there a way(any other supported proc sql function that can be used on Sybase) I can do this at the same time when I am extracting data?

Thanks everyone for your help.

Trusted Advisor
Posts: 3,214

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma
Contributor
Posts: 38

Re: Monotonic Function on Sybase Database

Hello,

Thanks for your help.

I am trying to apply the proc sort nodupkey functionality while extracting data from sybase to avoid another step of proc sort.

Would you have any suggestions for that.

With Select Distinct statement I am not sure if I can do it.

Out of 40 different variable i want to apply nodupkey only on 2 variables.

I would like to know if the select distinct statement would do that.

Thanks for your help.


Super Contributor
Posts: 307

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

It would be helpful to have a sampling of your tables/data and what you expect output to look like. But, yes, SELECT DISTINCT should do the job.

Contributor
Posts: 38

Re: Monotonic Function on Sybase Database

Hello All,

Method ONE (Two Steps):

proc sql;

create table one as

select *

from sashelp.class

;quit;

proc sort data = one nodupkey out=output_sort;

by sex age;

run;

Method TWO (One Step):

Same Output I can get by using MONOTONIC.

proc sql;

create table output_sql as

select * ,monotonic() as count

from sashelp.class

group by sex ,age

having min(count)=count

;quit;

Here I have used MONOTONIC. How can I get same result by having only 1 Step and without using MONOTONIC.

Super Contributor
Posts: 307

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

Another option is to use ods output. Example:

ods output SQL_Results=sql;

proc sql number;

   select distinct sex, age, mean(height) as avg_height, mean(weight) as avg_weight

   from sashelp.class

   group by sex, age

;

quit;

Contributor
Posts: 38

Re: Monotonic Function on Sybase Database

I want the Name variable also....

Super User
Posts: 19,822

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

Data really helps to visualize things.

It sounds like you want summary statistics remerged with the original data, and adding a row number.  Does that sound correct?

If so I believe you'll need a DoW loop, and I think it reads the data twice regardless of the "1 Step" idea, basically, its one step but two passes of the data anyways.

Super Contributor
Posts: 307

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

The sashelp.class dataset already has only one observation for each person(student). Using DISTINCT or GROUP BY will essentially have no effect if you are asking for unique combinations of NAME, SEX, AGE.

To demonstrate:

/* produce listing of variables of interest */

proc sql;

select name, age, sex, monotonic() as rownum

from sashelp.class;

;

quit;

/* use distinct */

proc sql;
select distinct name, age, sex, monotonic() as rownum
from sashelp.class
;
quit;

/* use group by, add count var to count each crossing of name/age/sex */

proc sql;
select name, age, sex, count ( * ) as count, monotonic() as rownum
from sashelp.class
group by name, age, sex
;
quit;

All three queries essentially produce the same result, since there is already only one row for each student (name).

Super User
Super User
Posts: 7,060

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

Sounds like you want a SYBASE method of numbering rows within groups.

You should look at the SYBASE documentation.

SyBooks Online

Trusted Advisor
Posts: 3,214

Re: Monotonic Function on Sybase Database

Posted in reply to ArpitSharma

It is documented that way insn't it. So the next step is code try and evaluate.

I don not know whether you Mysql will support multi-threading (excuting in parallel) having indexes on those keys etc. You can expect the results in any order as that is not defined with a dbms. Coding a "order by ..."for the resulting table will mysql do the sort. Yes it sorts.

"Avoiding steps"  that is indicating you are worried about performance-tuning. There are more atttention point on that part. The are many times not intuitive as needing some understanding of the effects.

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 1611 views
  • 6 likes
  • 6 in conversation