topic Transpose data using data step in New SAS User

Transpose data using data step

mlensing — Wed, 18 Nov 2020 23:20:36 GMT

Hi everyone,

I am attempting to transpose data using a data step (cannot use proc transpose for this question). I can't figure out why I keep getting a value of '15' for Maple Yr2009. I'm assuming it has something to do with the missing data, but if anyone has a suggestion for how I correct this that would be wonderful.

Dataset:

DATA Work.Trees;

INFILE DATALINES;

INPUT Type $6.

Year 8-11

HtFt 13-14;

CARDS;

Aspen 2009 15

Aspen 2010 16

Maple 2010 6

Maple 2011 8

Maple 2012 10

Spruce 2009 22

Spruce 2010 23

Spruce 2011 24

Spruce 2012 25

;

Current code to transpose using data step:

DATA Work.TreesWide2;

SET Work.Trees;

BY Type;

RETAIN Yr2009 Yr2010 Yr2011 Yr2012;

IFFIRST.LabDate = 1THENCALL MISSING(Yr2009, Yr2010, Yr2011, Yr2012);

IFYear = 2009 THEN Yr2009 = HtFt;

ELSE IF Year = 2010THEN Yr2010 = HtFt;

ELSE IF Year = 2011THEN Yr2011 = HtFt;

ELSE IF Year = 2012THEN Yr2012 = HtFt;

IF LAST.Type = 1;

DROP Year HtFt;

RUN;

Output I am currently getting:

Output I want:

Thank you!

Re: Transpose data using data step

novinosrin — Wed, 18 Nov 2020 23:26:08 GMT


DATA Work.Trees;
INFILE DATALINES;
INPUT Type $6.
Year 8-11
HtFt 13-14;
CARDS;
Aspen  2009 15
Aspen  2010 16
Maple  2010 6
Maple  2011 8
Maple  2012 10
Spruce 2009 22
Spruce 2010 23
Spruce 2011 24
Spruce 2012 25
;

data want;
 do until(last.type);
  set trees;
  by type;
  array yr(2009:2012) yr2009-yr2012;
  yr(year)=htft;
 end;
 drop htft;
run;

proc print noobs;run;

Type	Year	yr2009	yr2010	yr2011	yr2012
Aspen	2010	15	16	.	.
Maple	2012	.	6	8	10
Spruce	2012	22	23	24	25

Re: Transpose data using data step

ballardw — Wed, 18 Nov 2020 23:48:24 GMT

Can you form a study group with the person that posted the identical question?

https://communities.sas.com/t5/New-SAS-User/Help-with-restructuring-a-data-set-using-a-Data-Step-to-create-a/m-p/699969

You'll both learn more if doing a bit more coding together.

Re: Transpose data using data step

mlensing — Fri, 20 Nov 2020 21:57:12 GMT

Thank you!

Re: Transpose data using data step

RohanaBruker — Tue, 24 Nov 2020 17:31:05 GMT

Hi mlensing,

Here's another way to approach the question with arrays if your IF/THEN/ELSE statements aren't working:

DATA Work.TreesWide2;
SET Work.Trees;
BY Type;
ARRAY Years {2009:2012} Yr2009 - Yr2012 (. . . .);
IF First.Type = 1 THEN CALL MISSING (OF YEARS {*});
Years {Year} = HtFt;
IF Last.Type = 1;
DROP Year HtFt;
RUN;

Hopefully that helps!