BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dennis_oz
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

3 REPLIES 3
qoit
Pyrite | Level 9

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
Jade | Level 19

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 646 views
  • 3 likes
  • 4 in conversation