11-07-2013 09:10 AM
Monotonic is an undocumented feature:
11-07-2013 09:47 AM
further reading at http://support.sas.com/resources/papers/proceedings13/257-2013.pdf
I've added some extra notes to help you and fixed a typo the authors had.
The MONOTONIC function is similar to the internal variable _N_ in DATA Step. We can use it to
select the records according to their row number. For example, we choose the SSNs from the 501th
line to 800th line in the SSN dataset.
****(1) MONOTONIC: specify row numbers******************************;
where monotonic() between 501 and 800
Proc SQL works in sets unlike the datastep that reads/writes line by line.
The WHERE clause is always evaluated first, so in the code above we are putting everything in line 501 through 800 into log without reading everything.
Data step version would need the lines 1)"if _N_ >=501 and _N_ <=800", and would require the entire dataset to be read. or 2) use obs= and I think firstobs= when you read in the data.
The COALESCE function does the magic to combine multiple rows into a single one with any non-missing
value. In this example, there are two rows of SSNs, and supposedly they should be identical each other.
However, some of them are missing due to input errors or other reason. The COALESCE function in the
SQL statement below checks the value of the two rows and returns the first non-missing value, which
maximizes the SSN information.
****(3) COALESCE: combine values among columns**********************;
select monotonic() as obs,/*The use of this function is to just report the line number*/
coalesce(ssn1, ssn2) as ssn format = ssn11.
11-07-2013 10:12 AM
I found a use for COALESCE many years ago on the internet and it has helped me out in a lot of situations.
COALESCE selects the first non-null value in the function so it can be used in an SQL statement like this:
Where Field = COALESCE(@param, Field)
If @param is not null then it would be selected and the 2nd param(in this case Field) would be ignored and you would get filtered results from the Table.
If @param is null then the next param would be selected. Where Field = Field is true so you would get all the results from the Table.
I haven't tried this in SAS Proc SQL, but I assume it's available.