01-15-2016 04:28 PM - edited 01-15-2016 06:23 PM
Wondering how I can get my raw data to look like the final product I drafted. Basically, I have distinct user IDs and product categories, and for each product combination I want the total number of distinct users. How many user IDs only are present in category A, how many are A+B, A+C, B only, B+C, and so on... The end product would have a mirror image as A+B is the same as B+A... Any help would be much appreciated!
|raw data example|
|final product example|
01-15-2016 10:09 PM
So you'll never see a row with all zero's or all one's?
I'm not actually sure how to do this, but I have an idea of how to start perhaps. I'm used to seeing it requested differently and I'll post a link to that solution at the bottom. It would count all occurences of a, ab, ac, bc etc... but that means there's double counting.
data step2; set have; array vs(*) a b c; length row_name $2.; do i=1 to dim(vs); if vs(i)=1 then row_name=catt(row_name, vname(vs(i))); end; if lengthn(row_name)=1 then do; row1=row_name;row2=row_name; end; else do; row1=substr(row_name, 1, 1); row2=substr(row_name, 2, 1); end; keep id a b c row1 row2; run; proc freq data=step2; table row1*row2/sparse; run;
Here's the usual solution I see requested:
01-15-2016 10:44 PM - edited 01-15-2016 10:45 PM
01-15-2016 10:54 PM
To get the rest of the combinations you can change my code above. The proc freq will generate the table you want.
if lengthn(row_name)=1 then do; row1=row_name;row2=row_name; output; end;
else do; row1=substr(row_name, 1, 1); row2=substr(row_name, 2, 1); output;
temp=row1; row1=row2; row2=temp; output; end;
01-15-2016 11:21 PM
01-15-2016 11:47 PM
It doesn't matter what the names are. I called it row1,row2 but it's essentially the row/column identifier in the final output for the row. Is this row belongs in the ab/ba columns. If your names are longer or want to use the labels instead as, use CATX function and scan to separate them instead.
Post better data sample if you want more code, preferably in a data step.
01-16-2016 11:45 AM
01-16-2016 05:07 PM
Need further help from the community? Please ask a new question.