topic Re: Removing Special Characters from SAS Date in SAS Programming

Removing Special Characters from SAS Date

zoomzoom — Mon, 05 Oct 2015 19:15:21 GMT

Hello Members,

I have a list of variables from different tables and I am trying to filter results by finding special characters using Anypunct function. While it is working mostly, I have encountered few scenarios where the logic fails.

Example:

SAS brings in date from Oracle in this format DDMMMYYY:00:00:00

Anypunct considers colon ( : ) as a special character, flags the date field.

Note: I cannot treat date separately, as the variables come in one by one in a loop.

Similarly, for currency field, i.e. where there is an amount after decimal, ($72.50 or 52.33) the function considers that decimal as a special character.

What would be an efficient way to counter this?

Thank you in advance.

Re: Removing Special Characters from SAS Date

ballardw — Mon, 05 Oct 2015 19:17:44 GMT

What do you expect for output?

Re: Removing Special Characters from SAS Date

Reeza — Mon, 05 Oct 2015 19:20:30 GMT

Look at the unformatted values, strip the formats using either a put or format statement.

Re: Removing Special Characters from SAS Date

zoomzoom — Mon, 05 Oct 2015 20:54:34 GMT

I should have been clear with what I wanted !!

I would like to check special characters, but skip for date when the date format has colon in it, and skip currency when it has a decimal in it.

Re: Removing Special Characters from SAS Date

Smijoss — Mon, 05 Oct 2015 22:55:54 GMT

Is there a reason you are looking for those characters ?

The reason i ask is you do not need to do special coding to remove those special characters.

for example if you use sas informat Dollar15.2 format to read the amount as a number

similarly you can use sas informat anytm to convert Oracle timestamp to SAS timestamp

Re: Removing Special Characters from SAS Date

Astounding — Mon, 05 Oct 2015 23:07:48 GMT

Questions about what you have vs. what you need ... let's limit it to just the dates for now.

Do you really need to "filter" dates, or do you just need to find the proper date?

If you need to "filter", what does that mean?

Do you know which variables are supposed to represent dates?

Do you really have 3-digit months and 3-digit years?

Is the time portion always 00:00:00?

Related to the last 2 questions, perhaps you could give an example of what is actually in the data, and what it represents.

The answer is probably simple, but framing the problem might be more difficult.

Re: Removing Special Characters from SAS Date

Patrick — Mon, 05 Oct 2015 23:57:09 GMT

I would expect your date and currency column to be of SAS data type numeric. If so: There is no need to check for special characters in numeric variables and it certainly doesn't make sense to check the formatted values. Limit your checks to character values.

If anypunct() alone then still doesn't cut it for the character variables then you might consider using Regular Expressions to exclude certain patterns from checking.

Re: Removing Special Characters from SAS Date

Steelers_In_DC — Tue, 06 Oct 2015 12:59:55 GMT

you should use datepart()

date = datepart(DDMMMYYY:00:00:00)

These are not special characters, sas views this as a time and is formated to display date and time. Datepart() will drop the time.

Re: Removing Special Characters from SAS Date

Patrick — Tue, 06 Oct 2015 20:56:56 GMT

The code snippet you've posted is not valid SAS syntax.

date = datepart(DDMMMYYY:00:00:00)

Not sure what you're proposing here and why.