Transposing long data set into wide in SAS

joa2 — Sat, 21 Nov 2020 15:33:25 GMT

Hello, I am new to SAS and I am working with a large microarray data set. I need to transpose my long data to wide format. Example of my data is shown below with what the wide format should be.

Data MAlong

ID	SNPs	Genotype
23456	rs1234	CC
23456	rs1235	CC
23456	rs1236	TT
23456	rs1237	AA
23456	rs1238	TT
23456	rs1239	GG
23456	rs1240	GG
23456	rs1241	TT
23456	rs1242	CC
23456	rs1243	AA
17235	rs1234	TT
17235	rs1235	GG
17235	rs1236	TT
17235	rs1237	CC
17235	rs1238	AA
17235	rs1239	AA
17235	rs1240	AG
17235	rs1241	GG
17235	rs1242	GG
17235	rs1243	TC
25342	rs1234	AA
25342	rs1235	AG
25342	rs1236	AA
25342	rs1237	AG
25342	rs1238	TT
25342	rs1239	CC
25342	rs1240	--
25342	rs1241	GG
25342	rs1242	GG
25342	rs1243	GG

Data MAwide

ID	rs1234	rs1235	rs1236	rs1237	rs1238	rs1239	rs1240	rs1241	rs1242	rs1243
23456	CC	CC	TT	AA	TT	GG	GG	TT	CC	AA
17235	TT	GG	TT	CC	AA	AA	AG	GG	GG	TC
25342	AA	AG	AA	AG	TT	CC	--	GG	GG	GG

I am getting an error from my transpose code and figured that a macro may be needed in the code to do what I am looking for. An example of one of my code is shown below and I have modified with multiple ways

PROC TRANSPOSE data = MAlong out= MAWide ;
by ID;
var Genotype ;
ID SNPs;
run;

Can someone help me fix this code?

Thanks,

Joy

Re: Transposing long data set into wide in SAS

Kurt_Bremser — Sat, 21 Nov 2020 15:51:02 GMT

The only thing I see is that the data is not sorted in ascending order, so you need the NOTSORTED option:

PROC TRANSPOSE data = MAlong out= MAWide ;
by ID notsorted;
var Genotype;
ID SNPs;
run;

Re: Transposing long data set into wide in SAS

joa2 — Sat, 21 Nov 2020 16:05:57 GMT

Thanks, that seems to do the trick.

Best,
Joy

topic Re: Transposing long data set into wide in SAS in Statistical Procedures

Transposing long data set into wide in SAS

Re: Transposing long data set into wide in SAS

Re: Transposing long data set into wide in SAS