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

MBE_AnoOP — Fri, 19 Aug 2016 11:19:33 GMT

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,

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

Ksharp — Sat, 20 Aug 2016 06:20:05 GMT

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;

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

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

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