SQL procedure in SAS - Left function in where statement

[nicnad]

Hi,

I have the following code where I want to display records where right(montantoperation,2) = 00 AND left(dateoperation,4) = "2007"

How do I write this properly?

PROC SQL;

CREATE TABLE lctr.TXNS_2007_P_R AS SELECT TXNS.ApplProvenance,

  TXNS.DateOperation,

  TXNS.TypeCompte,

  TXNS.NoTransit,

  TXNS.NoCompte,

  TXNS.NoGuichet,

  TXNS.DateInscription,

  TXNS.MontantOperation,

  TXNS.CodeDevise,

  TXNS.MontantBilletEspece,

  TXNS.MontantMonnaie

FROM LCTR.TXNS AS TXNS

WHERE TXNS.TypeCompte = "1" AND right(TXNS.MontantOperation,2) = 00 AND left(TXNS.DateOperation,4) = "2007";

QUIT;

Thank you for your help and time.

[Doc_Duke · Accepted solution]

Nicnad,

Have you read the documentation?  That is not what the LEFT and RIGHT functions do.  They are character string functions that just align the non-space part to the left or right in the string.

If the date filed is a text string, then you can get the first 4 characters as 2007 by

left(TXNS.DateOperation,4) =: "2007"

(not the "=:" operator).

If it is a SAS Date variable, then it would be

year(TXNS.DateOperation) = 2007

It looks like MontantOperation is numeric, in which case you need to either convert it to a string and pick off the last two characters (check RIGHT, LENGTH, and SUBSTR documentation for syntax; there are other ways possible (like reversing the string)).  Or by using some mathematical identities.  If montantoperation is an integer, this should work

floor(montantoperation/100) = montantoperation

to pick off numbers that are multiples of 100.

Doc Muhlbaier

Duke

[nicnad]

Thank you Doc for your quick reply,

Substring and put worked well for me.

Here is my working code :

PROC SQL;

CREATE TABLE lctr.TXNS_2007_P_R AS SELECT TXNS.ApplProvenance,

  TXNS.DateOperation,

  TXNS.TypeCompte,

  TXNS.NoTransit,

  TXNS.NoCompte,

  TXNS.NoGuichet,

  TXNS.DateInscription,

  TXNS.MontantOperation,

  TXNS.CodeDevise,

  TXNS.MontantBilletEspece,

  TXNS.MontantMonnaie

FROM LCTR.TXNS AS TXNS

WHERE  substr(TXNS.DateOperation,1,4) = "2007"

AND TXNS.TypeCompte = "1"

AND substr(put(TXNS.MontantOperation,20.),length(put(TXNS.MontantOperation,20.))-2,2) = "00"

;QUIT;

Best regards,

nicnad