BookmarkSubscribeRSS Feed
Kyojik
Obsidian | Level 7

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

 

TickeryearShares_heldOwner1Owner2Owner3Owner4Owner5Owner6Owner7Owner8Owner9
Company2019Asif 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.0211.815.220.0190.0170.0170.0170.0170.017
7 REPLIES 7
ed_sas_member
Meteorite | Level 14

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
Obsidian | Level 7
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
Meteorite | Level 14

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
Obsidian | Level 7
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
Obsidian | Level 7

Here is my datafile.

ed_sas_member
Meteorite | Level 14

Thank you @Kyojik 

 

Could you please provide it as datalines.

I cannot open your file for security reasons.

 

best,

ed_sas_member
Meteorite | Level 14

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 728 views
  • 1 like
  • 2 in conversation