So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes (non-technical staff) is that setting up a database for the data and doing our work on a database instead of the massive flat files (SPS files mostly or the lesser known Triple-S) we currently work on.
I would likely be the one that would be working within this database to do all the data cleaning, validation and variable creation and so I'd like to make sure what I imagine is possible and to also see other possible options.
The data we are using is respondent level survey data. We will be updating the database every quarter with about 40,000 respondents and we have about 40,000 vectors of data per respondent (largely sparse data).
One of the more advanced calculations and operations I would need to do is, I would need to determine the standard deviation of a vector of data and then reduce a respondent's response to no more then 2 standard deviations away from the mean.
Also, creating new vectors of data. (When grouping respondents we will often mark them as apart of 1 of many groups and I would need a vector containing this data to work off of and this would most likely become its own table with various other data.)
I guess I'm just looking for some guidance, I have no problem learning SQL or using MySQL or whatever but I don't know if those are the correct solutions or if I should be looking at something else all together. I know what I need to do to the data but I don't know the best platform to do it on.
I apologize if this is too broad a subject but any guidance is appreciated.