topic Re: Splitting a data set by unique values of a variable in SAS Programming

Splitting a data set by unique values of a variable

Melk — Thu, 18 May 2017 17:23:34 GMT

I have a dataset with the following variables: ID (character), genotype (character). The dataste looks like this

ID gene

A1 WT

A2 Het

A3 Hom

B1 Het

B2 Hom

C1 WT

I want to create a table with the distinct values of gene as the column headers and the ID variables as observations, and then export to an excel document. How can I go about doing this?

WT Het Hom

A1 A2 A3

C1 B1 B2

Re: Splitting a data set by unique values of a variable

Jagadishkatam — Thu, 18 May 2017 17:52:20 GMT

This is an untested code which you could try

data have;
input ID $2. gene $;
cards;
A1 WT
A2 Het
A3 Hom
B1 Het
B2 Hom
C1 WT
;

proc transpose data=have out=want;
id gene;
var id ;
run;

Re: Splitting a data set by unique values of a variable

art297 — Thu, 18 May 2017 17:55:45 GMT

I don't think @Jagadishkatam's code will work without some additional code, but here is a version using the same concept:

data have;
   input (ID gene) ($);
   cards;
     A1        WT
     A2        Het
     A3        Hom
     B1        Het
     B2        Hom
     C1        WT
     D1        WT
;

proc sort data=have out=need;
  by gene;
run;

data need;
  set need;
  by gene;
  if first.gene then _counter=1;
  else _counter+1;
run;

proc sort data=need;
  by _counter;
run;

proc transpose data=need out=want (drop=_:);
  by _counter;
  var id;
  id gene;
run;

Art, CEO, AnalystFinder.com

Re: Splitting a data set by unique values of a variable

Melk — Thu, 18 May 2017 18:07:31 GMT

Worked perfectly, thanks!