turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- SQL procedure in SAS - Left function in where stat...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2013 11:22 AM

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.

Accepted Solutions

Solution

03-20-2013
11:50 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nicnad

03-20-2013 11:50 AM

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

All Replies

Solution

03-20-2013
11:50 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nicnad

03-20-2013 11:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Doc_Duke

03-20-2013 02:00 PM

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