I feel like this should be so easy and straight forward but I'm struggling to figure out how to do this...
There are many times I want to filter for values "In a list" where it's a long list of items...perhaps a list of values from Excel, or values from a Word doc I've saved. I would love to just copy all the values and paste them all in at once. But when I go to "Add" and paste the values, it only pastes in the first value in the list. It's time consuming to copy and paste each value individually and clicking through a "Get Values" list is not doable when there are hundreds/thousands of values to choose from.
I tried poking around the "Advanced Filter" option but was still not able to figure it out. Any guidance would be appreciated.
Here's an example of a use case:
I have a table with hundreds of Product Group IDs - I'd like to filter this table to only certain Product Group IDs that I have saved and categorized in an Excel file. When creating the Query Builder, I'd like to simply filter on Product Group ID, use the "In a list" option, be able to copy all the IDs from my Excel list and paste them so they are all in my filter list.
I know there are options to import my external file but the size of the data I'm working with is so large that when I join to other tables it takes longer to query. I would like to avoid any joins and simply just filter the single table itself.
Thank you!
That won't be possible. Using point & click only you will need to import your Excel and then define an inner join.
You could alternatively also create a format with an other and then use this format in a where clause and only select the cases that don't match the other condition.
If writing code you could also use a data step, load the Excel data into a hash lookup table and then use the hash check() method to select the rows.
That won't be possible. Using point & click only you will need to import your Excel and then define an inner join.
You could alternatively also create a format with an other and then use this format in a where clause and only select the cases that don't match the other condition.
If writing code you could also use a data step, load the Excel data into a hash lookup table and then use the hash check() method to select the rows.
Thank you for the options! Was hoping there was a trick to "copy and paste" but doesn't sound like it.
I'm not familiar with a Format or a hash lookup table...I will google to learn more.
Thanks for your time and help!
A quick and dirty trick I sometimes use in Excel is to create a second column that formats the codes as needed, so it adds quotes and commas and then I can copy that list and paste it into my code more easily.
e.g. (untested)
B2
=concatenate("'", A2, "") (quotes are double quote, single quote, double quote)
If I'm feel more lazy:
C2 = B2
C3 = concatenate(b2, ", ", b3)
And drag that down.
Then I use the values from the last column to copy/paste into my code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: