Help using Base SAS procedures

Aggregate into Bins

Reply
New Contributor
Posts: 3

Aggregate into Bins

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.

New Contributor
Posts: 3

Aggregate into Bins

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?

Super User
Posts: 10,035

Aggregate into Bins

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

New Contributor
Posts: 3

Re: Aggregate into Bins

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;


Super User
Posts: 10,035

Re: Aggregate into Bins

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

Ask a Question
Discussion stats
  • 4 replies
  • 322 views
  • 1 like
  • 2 in conversation