topic Re: Help with restructuring a data set using a Data Step to create a new dataset in New SAS User

SAS class R

RuthE — Tue, 26 Jan 2021 13:37:00 GMT

Re: Help with restructuring a data set using a Data Step to create a new dataset

PeterClemmensen — Wed, 18 Nov 2020 20:02:25 GMT

It can be done much simpler with an array using the years of interest as indexes

data TreesWide2;
   set Trees;
   array _ {2009 : 2012} _2009 - _2012;
   do until (last.Type);
      set Trees;
      by Type;
      _ [Year] = HtFt;
   end;
run;

Result:

Type   Year  HtFt _2009 _2010 _2011 _2012 
Aspen  2010  16    15   16    .     . 
Maple  2012  10    .    6     8     10 
Spruce 2012  25    22   23    24    25

Re: Help with restructuring a data set using a Data Step to create a new dataset

ballardw — Wed, 18 Nov 2020 20:25:57 GMT

What is wrong with using a proper tool like Proc Transpose?

When you have errors it is best practice to copy the log information with the entire data step or procedure code and all notes, warnings and errors. Paste the copied text into a code box opened on the forum with the </> icon to preserve formatting and appearance of the diagnostics that SAS often supplies with errors.

You are referencing 4 variables that do not exist in your data: yr2009, yr2010 etc. So they have no values.

Your variable HtFt is never text and has values like 15, 16, 20, not 2009, 2010, 2011 or 2012. So you are 1) going to get conversion messages in the log and 2) none of the comparisons will ever be true so all of your _2009 etc variables will always be missing.

Re: Help with restructuring a data set using a Data Step to create a new dataset

tmlee02 — Thu, 10 Dec 2020 15:15:56 GMT

Instead of using an array, you could also edit your DATA step to use the following

IF Year = '2009' THEN Yr2009 = HtFt;

Re: Help with restructuring a data set using a Data Step to create a new dataset

PeterClemmensen — Mon, 30 Nov 2020 09:41:32 GMT

@RuthE Did this solve your problem?

Re: Help with restructuring a data set using a Data Step to create a new dataset

adhunamukherjee — Mon, 14 Dec 2020 20:50:05 GMT

Hi Ruth,

I am not sure if you have found the solution already. The below code runs fine for me:

DATA	WORK.TreesWide2;
	SET	Work.Trees ;
	BY	Type;

	RETAIN	_2009 _2010 _2011 _2012;

	IF	FIRST.Type = 1	THEN	CALL MISSING (_2009, _2010, _2011 , _2012);
	IF			Year = '2009' THEN _2009 = HtFt;
		ELSE IF Year = '2010' THEN _2010 = HtFt;
		ELSE IF Year = '2011' THEN _2011 = HtFt;
		ELSE IF Year = '2012' THEN _2012 = HtFt;

	IF	LAST.Type = 1;
    DROP Year HtFt;
RUN;

This generated the following wide dataset:

Type	_2009	_2010	_2011	_2012
Aspen	15	16	.	.
Maple	.	6	8	10
Spruce	22	23	24	25

Please let me know if this doesn't work for you.

Thanks,

Adhuna