Help using Base SAS procedures

SQL procedure in SAS - Left function in where statement

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

SQL procedure in SAS - Left function in where statement

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
Trusted Advisor
Posts: 2,113

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

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

View solution in original post


All Replies
Solution
‎03-20-2013 11:50 AM
Trusted Advisor
Posts: 2,113

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

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

Regular Contributor
Posts: 186

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 149 views
  • 0 likes
  • 2 in conversation