adding a (python) list to project suite variable from database

     ...but here its storing only the last loop variable.

This is because you have used a wrong indentation as well as have declared variables in the wrong places. Try this:

def dbarea():
	area_string = ProjectSuite.Variables.Location    
	all_areas = area_string.split(",")
	data_container = []
	
	for area in all_areas:   
		AConnection = ADO.CreateADOConnection()    
		AConnection.ConnectionString = "Provider=MSDASQL.1;" + \
					       "Data Source=LMk2 Penelope"     
		AConnection.LoginPrompt = False
		AConnection.Open()  
		RecSet = AConnection.Execute_('select sare_no from sare where short_name={}'.format(area))
		RecSet.MoveFirst()
   
		while not RecSet.EOF:       
			rows = RecSet.Fields.Item["Sare_no"].Value
			data_container.append(rows)      
			RecSet.MoveNext()  
	  
		AConnection.Close()
   
       dbcount = len(data_container)
       Log.Message("The Database Count is : {}".format(dbcount))
       ProjectSuite.Variables.AddVariable("Var5","String")
       ProjectSuite.Variables.Var5 = str(data_container)

Also note, that splitting of your string: "North, South, East , South East , West" will return:

["North",
" South",
" East ",
" South East ",
" West"]

With all spaces. So be careful.