topic Re: Aggregate into Bins in SAS Procedures

Aggregate into Bins

glennni — Tue, 27 Mar 2012 01:19:16 GMT

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.

Aggregate into Bins

glennni — Tue, 27 Mar 2012 03:17:38 GMT

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?

Aggregate into Bins

Ksharp — Tue, 27 Mar 2012 08:15:03 GMT

You need to post some sample data and output you need.

Re: Aggregate into Bins

glennni — Tue, 27 Mar 2012 08:58:22 GMT

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

proc import datafile="C:\Users\John\Desktop\test.csv"
     out=experiment
     dbms=csv
     replace;
     getnames=yes;

Re: Aggregate into Bins

Ksharp — Tue, 27 Mar 2012 09:49:36 GMT

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;

Ksharp