Transpose and Count (?)

[sasquest_J]

Hi All: 

I have data that is like this:

ColumnA	ColumnB
@1email	1232;0829
@2email	1232;0829;0938
@1email	0938;48294;2938
@4email	0829;0938;0384

my goal is to get this: 

Basically the count of column B each number before the ; - how many times they showed up 

ColumnA	ColumnB
1232	2
0829	3
0938	3
48294	1
2938	1
0384	1

[novinosrin · Accepted solution]

data have;
input (ColumnA	ColumnB	) (:$20.);
cards4;
@1email	1232;0829
@2email	1232;0829;0938
@1email	0938;48294;2938
@4email	0829;0938;0384
;;;;

data temp;
set have;
do _n_=1 to countw(columnb,';');
 v=scan(columnb,_n_,';');
 output;
end;
keep v;
run;

proc freq data=temp ;
tables v/out=want(keep=v count);
run;

[PaigeMiller]

It would help if your outputs corresponded to the inputs. Or if you explained how the outputs are derived from the inputs, that would also help.

As it is, I don't see how your fourth line of input corresponds to anything in the output, and then there are two extra lines of output that don't correspond to the inputs.

So please explain, and provide examples of input data and desired output consistent with the explanation.

[novinosrin]

Something fancy

data have;
input (ColumnA	ColumnB	) (:$20.);
cards4;
@1email	1232;0829
@2email	1232;0829;0938
@1email	0938;48294;2938
@4email	0829;0938;0384
;;;;

data _null_;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("v") ;
   h.definedata ("v","count") ;
   h.definedone () ;
 end;
set have end=z;
do _n_=1 to countw(columnb,';');
 v=scan(columnb,_n_,';');
 if  h.find() ne 0 then count=1;
 else count=sum(count,1);
 h.replace();
end;
if z;
h.output(dataset:'want');
run;