topic Re: transpose long to wide in SAS Programming

transpose long to wide

ansepans — Thu, 09 Jan 2020 12:45:08 GMT

I need to transpose my data but end up missing the observations with s. hominis when I do.

Data set have

ID biopsinumber indate bacteria

1 1 01012020 s. aureus

1 2 01012020 s. aureus

1 3 01012020 s. aureus

2 1 03042020 s. epidermidis

2 1 03042020 s. hominis

2 2 03042020 s. epidermidis

2 2 03042020 s. hominis

2 3 03042020 s. epidermidis

2 3 03042020 s. hominis

data set want

ID indate biopsinumber1 biopsinumber2 biopsinumber3 biopsinumber4 biopsinumber5 biopsinumber6

1 01012020 s. aureus s. aureus s. aureus

2 1 03042020 s. epidermidis s. epidermidis s. epidermidis s. hominis s. hominis s. hominis

proc transpose data=kamme1
out=transkamme1 (drop=_NAME_ _LABEL_) prefix=biopsinr_ let ;
by ID ;
id biopsinumber;
var bacteria;

copy indate;
run;

I use SAS university Edition for Mac.

I hope you can help me.

Re: transpose long to wide

novinosrin — Thu, 09 Jan 2020 12:56:39 GMT

data have;
input ID biopsinumber indate bacteria & $10.;
cards;
1 1 01012020 s. aureus
1 2 01012020 s. aureus
1 3 01012020 s. aureus
2 1 03042020 s. epidermidis
2 1 03042020 s. hominis
2 2 03042020 s. epidermidis
2 2 03042020 s. hominis
2 3 03042020 s. epidermidis
2 3 03042020 s. hominis
;

proc transpose data=have out=want(drop=_name_) prefix=biopsinumber;
by id indate;
var bacteria;
run;

ID	indate	_NAME_	biopsinumber1	biopsinumber2	biopsinumber3	biopsinumber4	biopsinumber5	biopsinumber6
1	1012020	bacteria	s. aureus	s. aureus	s. aureus
2	3042020	bacteria	s. epiderm	s. hominis	s. epiderm	s. hominis	s. epiderm	s. hominis

Re: transpose long to wide

Kurt_Bremser — Thu, 09 Jan 2020 13:00:06 GMT

Simplify the transpose:

data have;
input ID $ biopsinumber $ indate :ddmmyy8. bacteria $20.;
format indate yymmddd10.;
datalines;
1 1 01012020 s. aureus
1 2 01012020 s. aureus
1 3 01012020 s. aureus
2 1 03042020 s. epidermidis
2 1 03042020 s. hominis
2 2 03042020 s. epidermidis
2 2 03042020 s. hominis
2 3 03042020 s. epidermidis
2 3 03042020 s. hominis
;

proc transpose
  data=have
  out=want (drop=_NAME_)
  prefix=biopsinr_
  let
;
by ID indate;
var bacteria;
run;

Re: transpose long to wide

ansepans — Thu, 09 Jan 2020 13:21:46 GMT

Perfect! Thank you for your help.