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:
Output I want:
Thank you!
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 |
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 |
Can you form a study group with the person that posted the identical question?
You'll both learn more if doing a bit more coding together.
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!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.