topic seperate data from a column in SAS Programming

seperate data from a column

dennis_oz — Mon, 22 Feb 2021 03:17:21 GMT

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

Re: seperate data from a column

Tom — Mon, 22 Feb 2021 03:21:19 GMT

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=p168a7v3fahigkn1pbaymi7g0jla.htm&locale=en

Re: seperate data from a column

qoit — Mon, 22 Feb 2021 03:52:36 GMT

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;

Re: seperate data from a column

andreas_lds — Mon, 22 Feb 2021 07:17:45 GMT

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;