?
Solved

How to output SAS dataset with variables without ASCII special characters or numbers?

Posted on 2013-05-21
5
Medium Priority
?
727 Views
Last Modified: 2013-05-26
I am trying to output SAS listing of variable "name" without ASCII special characters or numbers?

Below code is where variable "name" contains special characters and numbers. I just also would like to output datasets "data9" and "data10" from dataset "combined" without special characters or numbers.  

proc sql;
    create  table data5 as
    select unique
          name, Var4,
          Var5
    from combined
   where (anyPunct(name) > 0);
 
    create  table data6 as
    select unique
          name, Var4,
          Var5
    from combined
where (anyPunct(name) > 0);
quit;

proc sql;
    create  table data7 as
    select unique
          name, Var4,
          Var5
    from combined
   where (anyDigit(name) > 0);
 
    create  table data8 as
    select unique
          name, Var4,
          Var5
    from combined
where (anyDigit(name) > 0);
quit;

Open in new window

0
Comment
Question by:labradorchik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:labradorchik
ID: 39184265
I already tried below code but I had many errors and datasets "data9" and "data10" were not created.  

proc sql;
    create  table data9 as
    select unique
          name, Var4,
          Var5
    from combined
   where ((anyPunct(name) = 0) and (anyDigit(name) = 0));
 
    create  table data10 as
    select unique
          name, Var4,
          Var5
    from combined
where ((anyPunct(name) = 0) and (anyDigit(name) = 0));
quit;

Open in new window

0
 
LVL 11

Assisted Solution

by:theartfuldazzler
theartfuldazzler earned 2000 total points
ID: 39185200
Hi

Try using the compress function :

Name2 = compress(name,, "ck") ;

If I remember correctly, the "ck" will Keep Characters only and drop all numbers and special characters.
0
 

Author Comment

by:labradorchik
ID: 39185666
Below code works and outputs variable "name" without any special characters or numbers but I also would like to create another filter:

if Var5  > 5  & < 20 then output to data9 and data10
How may I incorporate this to my already existing "where" statements

proc sql;
    create  table data9 as
    select unique
          name, Var4,
          Var5
    from combined
   where (anyPunct(name) = 0) & (anyDigit(name) = 0);
 
    create  table data10 as
    select unique
          name, Var4,
          Var5
    from combined
where (anyPunct(name) = 0) & (anyDigit(name) = 0);
quit;

Open in new window


I tried below code but it still does not recognize Var5  > 5  & < 20, although, surprisingly no errors. Any comments or suggestions?

proc sql;
    create  table data9 as
    select unique
          name, Var4,
          Var5
    from combined
   where ((anyPunct(name) = 0) & (anyDigit(name) = 0)) & 
   where ((Var5 > 5 | Var5 < 20));
 
    create  table data10 as
    select unique
          name, Var4,
          Var5
    from combined
where (anyPunct(name) = 0) & (anyDigit(name) = 0) &
where ((Var5 > 5 | Var5 < 20));
quit;

Open in new window

0
 

Accepted Solution

by:
labradorchik earned 0 total points
ID: 39185910
theartfuldazzler, thank you very much for your comments!

Your example worked partially but below code worked perfectly:

where (anyPunct(name) = 0) and (anyDigit(name) = 0) and Var5 > 5 and Var5 < 20;

Open in new window

0
 

Author Closing Comment

by:labradorchik
ID: 39197455
The best possible answer was found while testing the code.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question