topic Re: Count occurrances of a variable's value in SAS Programming

Count occurrances of a variable's value

Suzy_Cat — Sun, 02 Feb 2020 06:49:00 GMT

hi all; may you please assist. I have a dataset where the client column can have multiple clients. I need count the occurrences of the clients showing in the data.

Below is the example of dataset: Count is the data column needed here, ie, Client A appeared 4 times in the data while B showed 5 times.

Client	Count
A
B
A,B
B,A
A,B,C
B
C
D

data wanted:

Client	Count
A	4
B	5
C	2
D	1

thanks ahead of time for your assistance.

Re: Count occurrances of a variable's value

unison — Sun, 02 Feb 2020 07:10:02 GMT

Use an intermediate step to separate your client variable into 1 client per observation (using scan). Then use proc freq to count the instances of each client:

data have;
	input client :$50.;
	datalines;
A
B
A,B
B,A
A,B,C
B
C
D
;
run;

data temp;
	set have;
	i=1;

	do until (scan(client, i, ',')='');
		client_separated=compress(scan(client, i, ','));
		output;
		i+1;
	end;
	drop i client;
run;

proc freq data=temp noprint;
	tables client_separated / out=want(drop=percent);
run;

Re: Count occurrances of a variable's value

Suzy_Cat — Sun, 02 Feb 2020 07:20:59 GMT

hi unison, that is super. it is exactly what I need to achieve!

Re: Count occurrances of a variable's value

novinosrin — Sun, 02 Feb 2020 14:41:24 GMT


data have;
	input client :$50.;
	datalines;
A
B
A,B
B,A
A,B,C
B
C
D
;
run;

data _null_ ;
 if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("client") ;
   h.definedata ("client","count") ;
   h.definedone () ;
 end;
 set have(rename=client=_client) end=z;
 length client $10;
 do _n_=1 to countw(_client,',');
  client=scan(_client,_n_,',');
  if h.find() = 0 then count=sum(count,1);
  else count=1;
  h.replace();
 end;
 if z;
 h.output(dataset:'want');
run;

proc print noobs;run;

Re: Count occurrances of a variable's value

novinosrin — Sun, 02 Feb 2020 16:54:47 GMT


data have;
	input client :$50.;
	datalines;
A
B
A,B
B,A
A,B,C
B
C
D
;
run;

data want;
 do until(z);
  set have end=z;
  array cl(999999) $10 _temporary_;
  array co(999999) _temporary_;
  length temp $10;
  do _n_=1 to countw(client,',');
   temp=scan(client,_n_,',');
   k=whichc(temp, of cl(*));
   if k then co(k)=sum(co(k),1);
   else do;
    _iorc_+1;
    cl(_iorc_)=temp;
	co(_iorc_)=1;
   end;
  end;
 end;
 do _n_=1 to dim(cl)-cmiss(of cl(*));
  client=cl(_n_);
  Count=co(_n_);
  output;
 end;
 keep client count;
run;

Re: Count occurrances of a variable's value

Suzy_Cat — Wed, 05 Feb 2020 23:53:12 GMT

Amazing!!

Thank you guys for the great help. The alternative methods helps me to learn. :):)

Re: Count occurrances of a variable's value

Suzy_Cat — Wed, 05 Feb 2020 23:53:34 GMT

all works!