BookmarkSubscribeRSS Feed
glennni
Calcite | Level 5

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.

4 REPLIES 4
glennni
Calcite | Level 5

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?

Ksharp
Super User

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

glennni
Calcite | Level 5

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;


Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1049 views
  • 1 like
  • 2 in conversation