03-26-2012 09:19 PM
I have a CSV file that contains experimental data. Each row in this set represents a different experiment. The columns are the name of the design, which lab it was tested in, drug used, and the response measured.
I would like to aggregate this data set into bins. So,rather than each row representing a different experiment, each row will now represent a specific response range of 100mg for each design, lab, and drug combintation.
So, it have these columns: Design, Lab, Drug, Response range (namely 100-200,200-300,300-400,400-500) and then the number of experiments who yielded a response in that range, in that lab, that design, and using that drug.
Can anyone help with this? I am new to this software, but really really need some assistance on this one. Thank you for any information and your time.
03-26-2012 11:17 PM
Does my question make sense? Or is more information needed.
I input the data. I know how to sort. But I dont now how group data that fits in the same cateogry and create a total. Does this require a marco?
03-27-2012 04:58 AM
okay! No problem.
For simplicity, lets say there are two drugs(Coinhibitor and THI) and two labs (Marley and Johnson).
And lets say my data looks like this
Each line represents a different experiment
Lab Drug Response(mg)
Marley Coinhibitor 157
Marley THI 201
Johnson Coinhibitor 214
Marley Coinibitor 112
Johnson THI 345
Marley THI 342
Johnson Coinhibitor 247
Marley THI 345
Here is what I want it too look like based on the results above
Range of Response Lab Drug Amount of Experiments that fit under this combtionation
100-200 Johnson Coinhibitor 0
200-300 Johnson Coinhibitor 2
300-400 Johnson Coinhibitor 0
100-200 Johnson THI 0
200-300 Johnson THI 0
300-400 Johnson THI 1
100-200 Marley Coinhibitor 2
200-300 Marley Coinhibitor 0
300-400 Marley Coinhibitor 0
100-200 Marley THI 0
200-300 Marley THI 1
300-400 Marley THI 2
So essentially, I group the responses by 100 range intervals and then count how many experiments fit within each combination of lab, drug, and range.I
Can this type of thing be done?
So far, I have just imputted my data into SAS from a csv excel file
03-27-2012 05:49 AM
OK. See this.
data x; input Lab $ Drug $ Response; cards; Marley Coinhibitor 157 Marley THI 201 Johnson Coinhibitor 214 Marley Coinibitor 112 Johnson THI 345 Marley THI 342 Johnson Coinhibitor 247 Marley THI 345 ; run; proc format ; value fmt 100-<200='100-200' 200-<300='200-300' 300<-400='300-400' ; run; options missing=0; proc freq data=x ; tables lab*drug*response/list missing nopercent nocum; format response fmt.; run;