topic Transpose and Count (?) in SAS Programming

Transpose and Count (?)

sasquest_J — Mon, 02 Mar 2020 01:17:11 GMT

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

Re: Transpose and Count (?)

novinosrin — Mon, 02 Mar 2020 01:26:27 GMT


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;

Re: Transpose and Count (?)

PaigeMiller — Mon, 02 Mar 2020 01:28:41 GMT

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.

Re: Transpose and Count (?)

novinosrin — Mon, 02 Mar 2020 01:40:11 GMT

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;