How do i concatenate through group in SAS 9.4 , urgent help needed

[MBE_AnoOP]

Hi All,

Pls help me out to get the desired output from the below sas file layout : 

Sas file layout:

AccountName    Sector                             Centrename                                         District                    State

Aptech Limited	Media and Entertainment	ANDHERI WEST-ARENA	Mumbai	Maharashtra
Aptech Limited	Security	BORIVALI-ARENA	Mumbai	Maharashtra
Aptech Limited	Security	C.G. ROAD-ARENA	Ahmedabad	Gujarat
Aptech Limited	Media and Entertainment	SION-ARENA	Nagpur	Maharashtra
Aptech Limited	Agriculture	SURAT, CITYLIGHT-ARENA	Surat	Gujarat
Aptech Limited	Media and Entertainment	VILE PARLE WEST-ARENA	Mumbai	Maharashtra
Brainware ltd	Organised Retail	Brainware Consultancy Pvt Ltd. (Howrah)	Howrah	West Bengal
Brainware ltd	Organised Retail	Brainware Consultancy Pvt Ltd. (Saltlake)	Kolkata	West Bengal
Brainware ltd	Healthcare	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal
Brainware ltd	Organised Retail	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal
Brainware ltd	Security	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal

Desired Output:

AccountName	count_centrename	count_District	District	Sector	State
Aptech Limited	4	2	Mumbai,Nagpur	Media and Entertainment,Security	Maharashtra
Aptech Limited	2	2	Ahmedabad,Surat	Security, Agriculture	Gujrat
Brainware Ltd	3	3	Howrah,Kolkata,Parganas North	Organised Retail,Healthcare,Security	West Bengal

1.  'district' and 'sector' should have distinct values only.

2. Count of centre should be for distinct training centre only.

3. 'AccountName' in layout file is not in sorted order.

Thank you to anyone who can help me to get the desired output.

Warm Regards,

[Ksharp]

That is very cumbersome.


data have;
infile cards dlm='09'x truncover;
input (AccountName Sector   Centrename  District  State) ( : $80.);
cards;                                      
Aptech Limited	Media and Entertainment	ANDHERI WEST-ARENA	Mumbai	Maharashtra
Aptech Limited	Security	BORIVALI-ARENA	Mumbai	Maharashtra
Aptech Limited	Security	C.G. ROAD-ARENA	Ahmedabad	Gujarat
Aptech Limited	Media and Entertainment	SION-ARENA	Nagpur	Maharashtra
Aptech Limited	Agriculture	SURAT, CITYLIGHT-ARENA	Surat	Gujarat
Aptech Limited	Media and Entertainment	VILE PARLE WEST-ARENA	Mumbai	Maharashtra	 	 	 	 
Brainware ltd	Organised Retail	Brainware Consultancy Pvt Ltd. (Howrah)	Howrah	West Bengal
Brainware ltd 	Organised Retail	Brainware Consultancy Pvt Ltd. (Saltlake)	Kolkata	West Bengal
Brainware ltd 	Healthcare	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal
Brainware ltd 	Organised Retail	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal
Brainware ltd 	Security	Brainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]	Parganas North	West Bengal
;run;
proc sort data=have;
 by AccountName State;
run;
data want;
 set have;
 by AccountName State;
 length new_distric new_sector $ 20000;
 array c{99999} $ 100 _temporary_;
 array d{99999} $ 100 _temporary_;
 array s{99999} $ 100 _temporary_;

 if first.State then do;
  call missing(of c{*} d{*} s{*});
  count_centrename=0; 
  count_District=0;
  count_Sector=0;
 end;
 
 if Centrename not in c then do;
  count_centrename+1;
  c{count_centrename}=Centrename;
 end;
 if District not in d then do;
  count_District+1;
  d{count_District}=District;
 end;
 if Sector not in s then do;
  count_Sector+1;
  s{count_Sector}=Sector;
 end;
 
 if last.State then do;
  new_distric=catx(',',of d{*}); 
  new_sector=catx(',',of s{*});
  output;
 end;
 
keep AccountName State count_: new_: ;
run;