SAS Data Integration Filter with SCAN Function

[soules9219]

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

[ballardw]

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?

[soules9219]

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.

[ballardw · Accepted solution]

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. 

[soules9219]

Thanks that worked,

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

[ballardw]

---

@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.