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

Reply
New User
Posts: 1

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

[ Edited ]

 

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 LimitedMedia and EntertainmentANDHERI WEST-ARENAMumbaiMaharashtra
Aptech LimitedSecurityBORIVALI-ARENAMumbaiMaharashtra
Aptech LimitedSecurityC.G. ROAD-ARENAAhmedabadGujarat
Aptech LimitedMedia and EntertainmentSION-ARENANagpurMaharashtra
Aptech LimitedAgricultureSURAT, CITYLIGHT-ARENASuratGujarat
Aptech LimitedMedia and EntertainmentVILE PARLE WEST-ARENAMumbaiMaharashtra
     
     
Brainware ltdOrganised RetailBrainware Consultancy Pvt Ltd. (Howrah)HowrahWest Bengal
Brainware ltd Organised RetailBrainware Consultancy Pvt Ltd. (Saltlake)KolkataWest Bengal
Brainware ltd HealthcareBrainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]Parganas NorthWest Bengal
Brainware ltd Organised RetailBrainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]Parganas NorthWest Bengal
Brainware ltd SecurityBrainware Consultancy Pvt. Ltd. [Group of Institutions (Barasat)]Parganas NorthWest Bengal

 

Desired Output:

AccountName                                                                                                     count_centrename count_District  DistrictSector      State
Aptech Limited                                                                            42Mumbai,Nagpur Media and Entertainment,Security Maharashtra
Aptech Limited                                             22Ahmedabad,SuratSecurity, Agriculture  Gujrat
Brainware Ltd                                              33Howrah,Kolkata,Parganas North Organised Retail,Healthcare,SecurityWest 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,

 

 

 

Super User
Posts: 10,041

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

Posted in reply to MBE_AnoOP
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;

Ask a Question
Discussion stats
  • 1 reply
  • 191 views
  • 0 likes
  • 2 in conversation