topic SAS Data Integration Filter with SCAN Function in SAS Programming

SAS Data Integration Filter with SCAN Function

soules9219 — Thu, 10 Aug 2017 14:35:57 GMT

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

Re: SAS Data Integration Filter with SCAN Function

ballardw — Thu, 10 Aug 2017 14:43:11 GMT

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?

Re: SAS Data Integration Filter with SCAN Function

soules9219 — Thu, 10 Aug 2017 14:46:42 GMT

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.

Re: SAS Data Integration Filter with SCAN Function

ballardw — Thu, 10 Aug 2017 16:28:16 GMT

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.

Re: SAS Data Integration Filter with SCAN Function

soules9219 — Thu, 10 Aug 2017 17:02:53 GMT

Thanks that worked,

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

Re: SAS Data Integration Filter with SCAN Function

ballardw — Thu, 10 Aug 2017 18:01:02 GMT

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