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
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
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;
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
Worked perfectly, thanks!
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.