topic Re: count different strings in separated list in SAS Programming

count different strings in separated list

Filipvdr — Tue, 14 Jul 2015 08:48:34 GMT

Hello all,

I got a question, my dataset looks like this:

Name	Address	Gender	Tickets
John	Road Street	M	A102;A102;A103;A104;A105;A105
Jeff	High Street	M	A221;A222;A223;
Karla	Low Street	F	A202;A104;A021;A124;

I want to count the distinct ticketcodes for each line so my result dataset will have an extra column saying John has 4 different tickets, Jeff 3 and Karla 4. The number of tickets can be unlimited.

Re: count different strings in separated list

Patrick — Tue, 14 Jul 2015 08:57:59 GMT

See below:

data sample;

length tickets $200;

tickets='A102;A102;A103;A104;A105;A105';

tickets_ctn=countw(tickets,';','ot');

output;

tickets='A102';

tickets_ctn=countw(tickets,';','ot');

output;

tickets='';

tickets_ctn=countw(tickets,';','ot');

output;

stop;

run;

Re: count different strings in separated list

RW9 — Tue, 14 Jul 2015 08:59:34 GMT

Assuming the delimiter is always the same you can do it simply by counting the delimiter:

result=countc(tickets,";");

However I note that your first row is incorrect in the pattern. If that really is your data, then maybe a count with scan would be better:

data want;

set have;

i=1;

result=0;

do while (scan(tickets,i,";") ne "");

result=result+1;

end;

run;

Re: count different strings in separated list

Filipvdr — Tue, 14 Jul 2015 09:12:08 GMT

Patrick,RW9 i need to count the different strings. In my first example there was a ticketcode twice, but i only want to count it once.

Re: count different strings in separated list

Filipvdr — Tue, 14 Jul 2015 09:20:48 GMT

RW9, i modified your code and now it works, thanks:

data test;

a ="D02;D03;D04;D05;D06;D07;D08;D09";

output;

a ="D02;D02;D04;D04;D06;D06;D08;D08";

output;

run;

data want;

set test;

i=1;

result=0;

do while (scan(a,i,";") ne "");

last_scan = scan(a,i-1,";");

scan = scan(a,i,";");

if last_scan ne scan then result=result+1;

else result = result;

i = i+1;

end;

run;

Re: count different strings in separated list

RW9 — Tue, 14 Jul 2015 09:22:32 GMT

Ah, ok. Normalise your data then, it will make your life easier.

data inter (drop=tickets);

set have;

length ticket $20;

i=1;

do while (scan(tickets,i,";") ne "");

ticket=scan(tickets,i,";");

output;

i=i+1;

end;

run;

/* Now you have data in a way that you can do aggregates on it */

proc sql;

create table COUNTS as

select NAME,

ADDRESS,

GENDER,

count(distinct TICKET) as CNT

from INTER

group by NAME,

ADDRESS,

GENDER;

quit;

Re: count different strings in separated list

RW9 — Tue, 14 Jul 2015 09:23:29 GMT

Thats good, but it will only work if the duplicate is in the record before it. If thats your data great, otherwise maybe look at normalising.

Re: count different strings in separated list

Filipvdr — Tue, 14 Jul 2015 09:26:57 GMT

Yes its ordered sequentially. Thanks again

Re: count different strings in separated list

Patrick — Tue, 14 Jul 2015 10:06:04 GMT

...or good old RegEx:

data have;

a ="D02;D03;D04;D05;D06;D07;D08;D09";

output;

a ="D02;D02;D04;D04;D06;D06;D06;D08;D08";

output;

a ="D02";

output;

a ="";

output;

run;

data want;

set have;

count=countc(prxchange('s/(\w+\b)(;?\1)*/1/oi',-1,a),'1');

run;

Re: count different strings in separated list

Filipvdr — Tue, 14 Jul 2015 10:13:24 GMT

i don't know RegEx but it works good!

Re: count different strings in separated list

Patrick — Tue, 14 Jul 2015 10:19:10 GMT

Can be a bit hard to get into it but very worth learning SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition

Search the Internet for Perl Regular Expressions. The syntax is not a SAS thing and there are tutorials around.

Re: count different strings in separated list

BrunoSilva — Tue, 14 Jul 2015 10:23:52 GMT

Hello,

There is so many ways to acomplish the desired output, here another example

data _null_;
 
      length list $ 200;
      tickets="A202;A104;A021;A124;";
 
      count=0;
      list="";
      do j=1 to count(tickets,";")+1;
            elem=strip(scan(tickets,j,";"));
 
            if findw(list,elem,' ', 'R') eq 0 and elem ne "" then do;
                  list=catx(" ",list,elem);
                  count=count+1;
            end;
      end;
      put count=;
 
run;

Re: count different strings in separated list

Ksharp — Tue, 14 Jul 2015 12:32:08 GMT

Patrick,

What if

a ="D03;D02;D03;D04;D05;D06;D07;D08;D09";

Code: Program

data have;
  a ="D03;D02;D03;D04;D05;D06;D07;D08;D09";
  output;
  a ="D02;D02;D04;D04;D06;D06;D06;D08;D08";
  output;
  a ="D02";
  output;
  a ="";
  output;
run;
 
data want;
  set have;
  array x{99999} $ 40 _temporary_;
  count=0;call missing(of x{*});
  do i=1 to countw(a,';');
   temp=scan(a,i,';');
   if temp not in x then do;count+1;x{count}=temp;end;
  end;
  drop i temp;
run;

Re: count different strings in separated list

Patrick — Tue, 14 Jul 2015 12:37:23 GMT

The OP said it's in sequence.