topic Count substring 'Id' number of times appeared in SAS Programming

Count substring 'Id' number of times appeared

chapidi99 — Fri, 15 Oct 2021 13:18:22 GMT

Hello,

I have a data looks like this below. I would like to count number of times substring 'Id' appeared in the variable Practise. I tried below code but it does not work, my code generates only 0.

Raw Data:

Practise

Id: John, Id: Kerstin, Name: sunny,

Name: Kane, Id: Rohan

Id: Ranson, Name: X_, Id: Stary

Id:Ata, Name: Unknown, Id: Aka, Name, Unknown, Id: Prx

Expected Result:

Practise	Id_Count
Id: John, Id: Kerstin, Name: sunny,	2
Name: Kane, Id: Rohan	1
Id: Ranson, Name: X_, Id: Stary	2
Id:Ata, Name: Unknown, Id: Aka, Name, Unknown, Id: Prx	3

My SAS code:

data samp ;
set Clean;
count_Id = count(Practice, 'Id:');
put count_Id;
run;

Re: Count substring 'Id' number of times appeared

chapidi99 — Fri, 15 Oct 2021 13:20:01 GMT

Corrected code (Practise variable), but still doesn't work

data samp ;
set Clean;
count_Id = count(Practise, 'Id:');
put count_Id;
run;

Re: Count substring 'Id' number of times appeared

Patrick — Sat, 16 Oct 2021 06:04:56 GMT

@chapidi99 What doesn't work? Your code appears to return the expected result for your sample data.

data have;
  infile datalines truncover dlm='|';
  input Practise :$100. count_expected;
  datalines;
Id: John, Id: Kerstin, Name: sunny,|2
Name: Kane, Id: Rohan|1
Id: Ranson, Name: X_, Id: Stary|2
Id:Ata, Name: Unknown, Id: Aka, Name, Unknown, Id: Prx|3
;

data want;
  set have;
  count_Id = count(Practise, 'Id:');
  count_derived_1=count(Practise,'id:','i');
run;

Re: Count substring 'Id' number of times appeared

chapidi99 — Mon, 18 Oct 2021 07:57:58 GMT

@Patrick, thanks it works when I removed put statement.