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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.