12-16-2014 05:13 PM
I have distributors that feature a number of products. Some only 1, but others up to 25. I'd like to scan all 25 columns and return if a distributor features that product
The data is aligned as follows:
Distributor_ID First_Product Second_Product......................................25_Product
1 Apples Bananas
3 Bananas Grapes Kiwis
In EXCEL it's dead easy using COUNTIF(array,value). So, I can create a new column in Excel for each Product. Since Distributor #1 sells Apples, a variable APPLES would have a value of 1 (vs 0)
1) What's the best way to handle this in SAS?
2) If the products were concatenated into a single variable, best way in that case? EX (Apples|Bananas) for Distributor_ID=1
12-16-2014 05:19 PM
Not sure what you want your output to look like.
There's the whichC function which checks if a value is in a list of other variables, that returns the index of where apples occurred.
x=whichc('APPLES', of product1-product25);
If you need to know how many times APPLES was in the list I'd concatenate them and then use countW
want=countw('APPLES', catx(",", of product1-product25, 'i');
12-16-2014 05:30 PM
Apples will only happen once - same for all the fruits. I'm leaning toward your "concatenated" answer. I'm waiting to get SAS installed but know this challenge is part of a project I'm working on.
12-16-2014 05:41 PM
A better alternative is to flip your data and run proc freq and easier to query when in long format.
array fruits(25) fruit1-fruit25;
do i=1 to 25;
if fruits(i) ne . then fruit=fruit(i);
keep id i fruit;
proc freq data=long;
*All ID's that have apple: