seperate data from a column

[dennis_oz]

Hi all,

please help to achieve below .

data a;
input id  stores $50.;
datalines;
123 Target||Big W||Nordstorm||Macys
run;

want;
------
id  count
123 4

[Tom]

Are you asking how to count?  Did you check out the COUNTW() function?

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=fedsqlref&docsetTarget=p1...

[qoit]

If the below helps:

data a;
	input id  stores $50.;
	datalines;
123 Target||Big W||Nordstorm||Macys
run;

data want (drop=stores);
	set a;
	store_cnt = countw(stores,"||");
run;

[andreas_lds · Accepted solution]

Please check the following code:

data have;
	input id  stores $50.;
	datalines;
123 Target||Big W||Nordstorm||Macys
456 Source||x|y||Westwind||Harrods
run;

data want;
   set have;
   count = countw(stores, "||");
   drop stores;
run;

The result for id=456 seems to be wrong. Unfortunately, countw has no option allowing delimiters longer than one char. The following code replaces || with ~ and calls countw afterwards.

data want;
   set have;

   count_word = countw(stores, "||");
   count_better = countw(tranwrd(stores, '||', '~'), '~');

   drop stores;
run;