Proc Sql

Reply
Contributor
Posts: 35

Proc Sql

Hi ,

Could some one explain coalesce and monotonic in Proc Sql.

Thanks

Super Contributor
Posts: 282

Re: Proc Sql

N/A
Posts: 1

Re: Proc Sql

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******************************;

proc sql;

select *

from ssn_data

where monotonic() between 501 and 800

;quit;

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**********************;

proc sql;

select monotonic() as obs,/*The use of this function is to just report the line number*/

coalesce(ssn1, ssn2) as ssn format = ssn11.

from ssn_data

;quit;

Occasional Contributor
Posts: 18

Re: Proc Sql

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:

     Select *

     From Table

     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.

Charlie

Ask a Question
Discussion stats
  • 3 replies
  • 363 views
  • 0 likes
  • 4 in conversation