BookmarkSubscribeRSS Feed
zoomzoom
Obsidian | Level 7

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.

8 REPLIES 8
ballardw
Super User

What do you expect for output?

 

zoomzoom
Obsidian | Level 7

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.

Reeza
Super User
Look at the unformatted values, strip the formats using either a put or format statement.
Smijoss
Fluorite | Level 6

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 

Astounding
PROC Star

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.

Patrick
Opal | Level 21

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.

Steelers_In_DC
Barite | Level 11

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.

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2160 views
  • 0 likes
  • 7 in conversation