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!
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!
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.