spliting sentence into variables

[Kyojik]

Hello Experts,

I have Ticker, Year and Share_Held. and I want to create Owner1-owner9 from it. as shown below.

Is there any way I can do this in sas. I have a large data which contains multiple companies and each company has multiple years.

Any help will be appreciated. Thanks

Ticker	year	Shares_held	Owner1	Owner2	Owner3	Owner4	Owner5	Owner6	Owner7	Owner8	Owner9
Company	2019	Asif Jamil Malik (13.02); Tara Uzra Dawood (11.81); Ayaz Samad Dawood (5.22); Shafqat Sultana (0.019); Muhammad Izqar Khan (0.017); Ahmad Salman Munir (0.017); Tahir Mehmood (0.017); Syed Shabahat Hussain (0.017); Charmaine Hidayatullah (0.017); Syed Farhan Abbas (0.017)	13.02	11.81	5.22	0.019	0.017	0.017	0.017	0.017	0.017

[ed_sas_member]

Hi @Kyojik 

You can use this :

data have;
	Ticker = "Company"; output;
	Year = "2019"; output;
	Shares_held = 'Asif Jamil Malik (13.02); Tara Uzra Dawood (11.81); Ayaz Samad Dawood (5.22); Shafqat Sultana (0.019); Muhammad Izqar Khan (0.017); Ahmad Salman Munir (0.017); Tahir Mehmood (0.017); Syed Shabahat Hussain (0.017); Charmaine Hidayatullah (0.017); Syed Farhan Abbas (0.017)';output;
run;


data want;
	set have;
	call symputx ("nb", countw(compress(Shares_held,' ()','A'),";"));
	array owner(&nb);
	do i= 1 to &nb;
		Owner(i) = scan(compress(Shares_held,' ()','A'),i,";");
	end;
	drop i;
run;

TIP: The compress() function uses 'A' as modifier which means removing alphabetic characters from the original string.

[Kyojik]

Thanks for help.
I am getting following issue.

56 data want;
57 set Inst_single_PCT;
58 call symputx ("nb", countw(compress(Inst_single_PCT,' ()','A'),";"));
59 array owner(&nb);
-
22
200
WARNING: Apparent symbolic reference NB not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, an integer constant, *.

ERROR 200-322: The symbol is not recognized and will be ignored.

60 do i= 1 to &nb;
-
22
WARNING: Apparent symbolic reference NB not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, INPUT, PUT.

61 Owner(i) = scan(compress(Inst_single_PCT,' ()','A'),i,";");
62 end;
63 drop i;
64 run;

[ed_sas_member]

Hi @Kyojik 

Thank you for your feedback.

In the compress function, it seems that you mention the dataset name (Inst_single_PCT ?) and not the variable ?

[Kyojik]

Hello ed_sas_member,
my variable and dataset name was same. I changed my variable name but issue remains.
data want;
set Inst_single_PCT;
call symputx ("nb", countw(compress(inst_Single,' ()','A'),";"));
array owner(&nb);
do i=1 to &nb;
Owner(i) = scan(compress(inst_Single,' ()','A'),i,";");
end;
drop i;
run;

[Kyojik]

Here is my datafile.

[ed_sas_member]

Thank you @Kyojik 

Could you please provide it as datalines.

I cannot open your file for security reasons.

best,

[ed_sas_member]

Hi @Kyojik 

I have automatize the process in case you have several rows. Please try it if relevant.

I cannot help you more without datalines to figure out what is the issue.

Bst,

proc sql noprint;
	select max(countw(compress(Inst_single_PCT,' ()','A'),";")) into:max_nb from Inst_single_PCT;
quit;

data want;
	set Inst_single_PCT;
	array owner(&max_nb);
	do i= 1 to countw(compress(Inst_single_PCT,' ()','A'),";");
		Owner(i) = scan(compress(Inst_single_PCT,' ()','A'),i,";");
	end;
	drop i;
run;