DATA Step, Macro, Functions and more

SAS Data Integration Filter with SCAN Function

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

SAS Data Integration Filter with SCAN Function

I'm trying to filter out certain elements within a field e.g.:

 

Name:

Marcus, Halberstream

 

Where "Name" is the field and "Marchus, Halberstream" is an individual record

 

I'm currently using SCAN in the filter&sort section of "Create Table" in SAS DI in the following manner:

 

SCAN(Name,2,',')

 

It correctly returns "Halberstream" - the string I want to filter by - but when I put the logic in and run the table, I still see "Marcus, Halberstream" in the output table.

 

Any suggestions on what I may be doing wrong or a better solution? I thought about using SUBSTR except the records are of different length, and so I cannot set an arbitrary element start-stop location, but must use "," delimiter.

 

Regards,

Soules9219


Accepted Solutions
Solution
a week ago
Super User
Posts: 10,500

Re: SAS Data Integration Filter with SCAN Function

Are you wanting to exclude or include the given name? It sounds like exclude but I'm not sure.

 

I would expect a filter to let me do something like Scan(name,2,',') = 'Smith' to select or Scan(name,2,',') ne 'Smith' to exclude. Perhaps you didn't get to the part to indicate exclude. Or perhaps your comparison string is incorrect. In Base sas

scan("Marcus, Halberstream",2,',')

yields " Halberstream", with a leading blank.

The default

scan("Marcus, Halberstream",2) will treat the blank as a delimiter as well and the leadingblank is not in the result. 

View solution in original post


All Replies
Super User
Posts: 10,500

Re: SAS Data Integration Filter with SCAN Function

First I do not use or have access to DI, and possibly this question belongs in the Data Management as that is where DI goes.

 

With that, I would not expect Filter to change the actual values of anything. I would expect it to reduce a set to those values I specified.

Perhaps you are looking for a task to create a new variable based on an existing one?

New Contributor
Posts: 3

Re: SAS Data Integration Filter with SCAN Function

That's right, I am not trying to change or remove anything, just reduce the set by my filter.

 

So for example if if i have 600 records in my set, after the SCAN filter function, I still have 600 whereas it should be lower.

 

 

Solution
a week ago
Super User
Posts: 10,500

Re: SAS Data Integration Filter with SCAN Function

Are you wanting to exclude or include the given name? It sounds like exclude but I'm not sure.

 

I would expect a filter to let me do something like Scan(name,2,',') = 'Smith' to select or Scan(name,2,',') ne 'Smith' to exclude. Perhaps you didn't get to the part to indicate exclude. Or perhaps your comparison string is incorrect. In Base sas

scan("Marcus, Halberstream",2,',')

yields " Halberstream", with a leading blank.

The default

scan("Marcus, Halberstream",2) will treat the blank as a delimiter as well and the leadingblank is not in the result. 

New Contributor
Posts: 3

Re: SAS Data Integration Filter with SCAN Function

Thanks that worked,

 

I could not see that it was entering a leading space on my end.

 

 

Super User
Posts: 10,500

Re: SAS Data Integration Filter with SCAN Function


soules9219 wrote:

Thanks that worked,

 

I could not see that it was entering a leading space on my end.

 

 


The proportional fonts I believe are used in most of the table viewers can make a leading blank very skinny and only having had such issues before made me think of that as an option.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 84 views
  • 0 likes
  • 2 in conversation