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
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.
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?
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.
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.
Thanks that worked,
I could not see that it was entering a leading space on my end.
@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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.