topic Re: SQL procedure in SAS - Left function in where statement in SAS Procedures

SQL procedure in SAS - Left function in where statement

nicnad — Wed, 20 Mar 2013 15:22:22 GMT

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.

Re: SQL procedure in SAS - Left function in where statement

Doc_Duke — Wed, 20 Mar 2013 15:50:00 GMT

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

Re: SQL procedure in SAS - Left function in where statement

nicnad — Wed, 20 Mar 2013 18:00:45 GMT

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