Changing a wide dataset to a long dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Changing a wide dataset to a long dataset

I've looked at a handful of other similar questions (here, here, and here), but have not had success with the accepted answers I found.  I'm trying to transform a wide data set into a long data set, turning column names into rows with matching records adjacent to the old column names.  I can't seem to get the original column names to appear using my current code.

 

I have a wide dataset that looks like this:

DBNEmp_IDFiscalYearImprovement_planAction_PlanTimelineSupport_PlanAssessment_PlanMeeting1Meeting2Meeting3Meeting4Meeting5
07B025 46984920151) Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions. 1. For Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions: (Fall)

a. Explain and model expectations for classroom behavior for students. (September)
Refer to the timelines included at the end of each action step/ activity listed above.A Ramapo consultant will support you with differentiating instruction, pacing, and engaging students.In our second and third meetings, we will review the evidence from classroom observations and student work/learning 12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..
07B025 46984920171E: Designing Coherent Instruction: Design lessons that cognitively engage students through content, support, and pacing.


• Design lesson plans that are structured to support appropriate pacing within the lesson by including specific reasonable time frames for each portion of the lesson.  (Fall)
See above1) You will schedule inter-visitations to observe your classes in their classrooms (with their primary teacher) with a

focus on noticing the behavior management strategies and non-verbal cues used. 
In our second and third meetings, we will review the evidence from classroom

observations and student work/learning
16SEP2016:00:00:00.00012JAN2017:00:00:00.00025MAY2017:00:00:00.000..
02Q51296470220151) Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions. (2D)

2) Using questions and assessments regularly to diagnose evidence of learning.(3D)

3) Engage students in learning by monitoring pacing. (3C)


a. Explain and model expectations for classroom behavior for students. (September)

b. Post class rules in a visible place for students.  Include visuals for each rule so students know what they are (since your kindergarteners are not yet readers).(September)

c. Share standards of conduct with students' families. (September)
Refer to the timelines included at the end of each action step/activity listed above.1) Work with Deborah the Math coach, to develop your practice in differentiating instruction. She will support you in the cyclical practice of collecting data, using data to plan scaffolded supports and materials for students, and provide differentiated instruction.In our second and third meetings, we will review the evidence from classroom observations and student work/learning 12SEP2014:00:00:00.00021JAN2015:00:00:00.00027MAY2015:00:00:00.000..
01M51514081720161) Designing coherent instruction by differentiating to meet the needs of your learners thereby ensuring cognitive challenge.1. For Designing coherent instruction by differentiating to meet the needs of your learners thereby ensuring cognitive challenge:

Refer to the timelines included at the end of each action step/ activity listed above.1) A Gifted and Talented coach will support you throughout the year with designing coherent instruction and engaging students in learning.

In our second and third meetings, we will review the evidence from classroom observations and student work/learning21SEP2015:00:00:00.00022APR2016:00:00:00.00023JUN2016:00:00:00.000.

.

 

 

I need it to look like this:

DBNEmp_IDFiscalYearSectionTextMeeting1Meeting2Meeting3Meeting4Meeting5     
07B025 4698492015Action_plan1. For Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions: (Fall)

a. Explain and model expectations for classroom behavior for students. (September)
12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..     
07B025 4698492015TimelineRefer to the timelines included at the end of each action step/ activity listed above.12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..     
07B0254698492015Support_PlanA Ramapo consultant will support you with differentiating instruction, pacing, and engaging students.12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..     
07B0254698492015Assessment_PlanIn our second and third meetings, we will review the evidence from classroom observations and student work/learning 12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..    

 

 

 

I've tried to do this with an array:

data want;
set have;
array d ImprovementPlan -- AssessmentPlan;
do i = 1 to dim(d);
	Section = d{i};
        Text = d
	output;
	end;
keep DBN Emp_ID FiscalYear Section Text Meeting1	Meeting2	Meeting3	Meeting4	Meeting5; 
run;

 

But end up with this:

DBNEmp_IDFiscalYearSectionMeeting1Meeting2Meeting3Meeting4Meeting5
07B025 46984920151) Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions. 12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..
07B025 46984920151. For Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions: (Fall)

a. Explain and model expectations for classroom behavior for students. (September)
12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..
07B025 4698492015Refer to the timelines included at the end of each action step/ activity listed above.12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..
07B0254698492015A Ramapo consultant will support you with differentiating instruction, pacing, and engaging students.12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..
07B0254698492015In our second and third meetings, we will review the evidence from classroom observations and student work/learning 12SEP2014:00:00:00.00022JAN2015:00:00:00.00028MAY2015:00:00:00.000..

 

 

 

I appreciate any advice you have for me.


Accepted Solutions
Solution
‎08-22-2017 01:30 PM
Occasional Contributor
Posts: 19

Re: Changing a wide dataset to a long dataset

I was able to make PROC TRANSPOSE work using the following code:

 

PROC TRANSPOSE DATA=WORK.t_yoy
	OUT=flash.TRANSPOSED_yoy(LABEL="Transposed WORK.T2017")
	PREFIX=Text
	NAME=Section
	LABEL=Label
;
BY Emp_ID FiscalYear DBN;
	VAR ImprovementPlan ActionPlan TimeLinePlan SupportPlan AssessmentPlan;
	COPY DBN Emp_ID FiscalYear Meeting1 Meeting2 Meeting3 Meeting4 Meeting5;

RUN; QUIT;

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,615

Re: Changing a wide dataset to a long dataset

Your code:

data want;
set have;
array d ImprovementPlan -- AssessmentPlan;
do i = 1 to dim(d);
	Section = d{i};
        Text = d    /* ; is missing, d is an array-you cannot assign it to a variable */
	output;
	end;
keep DBN Emp_ID FiscalYear Section Text Meeting1	Meeting2	Meeting3	Meeting4	Meeting5; 
run;

What do you want to be in TEXT variable ? 

 

Why not use PROC TRANSPOSE ?

Occasional Contributor
Posts: 19

Re: Changing a wide dataset to a long dataset

[ Edited ]

I tried PROC TRANSPOSE, but it doesn't seem to get me the desired result (the section in a single column and a secondary column containing the text from the sections).

 

Code:

 

PROC TRANSPOSE DATA=WORK.t_yoy
	OUT=flash.TRANSPOSED_yoy(LABEL="Transposed WORK.T2017")
	PREFIX=Column
	NAME=Source
	LABEL=Label
;
	VAR ImprovementPlan ActionPlan TimeLinePlan SupportPlan AssessmentPlan;
	COPY DBN Emp_ID FiscalYear Meeting1 Meeting2 Meeting3 Meeting4 Meeting5;

RUN; QUIT;

Results:

 

DBNEmp_IDFiscalYearMeeting1Meeting2Meeting3Meeting4Meeting5SourceColumn1Column2Column3Column4
01M01546975920159/12/20141/22/20155/28/2015NULLNULLimprovementplan69.205391.88742106.291488.24503
01M01546975920179/16/20161/12/20175/25/2017NULLNULLactionplan597.351539.0728839.2384678.3113
01M01596730220159/12/20141/21/20155/27/2015NULLNULLtimelineplan14.238411.49006614.0728514.23841
01M01510811720169/21/20154/22/20166/23/2016NULLNULLsupportplan282.6159239.404409.7682220.5298
01M01511890020159/17/20141/16/20155/29/2015NULLNULLassessmentplan221.5232205.4636234.2715437.2517
01M02073578920179/26/20163/28/2017NULL12/15/2016NULL ....
01M02078521420179/16/2016NULLNULL11/4/201611/18/2016 ....
01M02016497620169/28/20154/21/20166/14/2016NULLNULL ....
Super User
Posts: 20,219

Re: Changing a wide dataset to a long dataset

When you have the data structure you have I find the array method easier than PROC TRANSPOSE. Were you unable to get the data step working or do you just want to see both options?
Super User
Posts: 20,219

Re: Changing a wide dataset to a long dataset

Section = vname(d(i));

text = d(i);

output;

 

 

Replace your Section and Text with the above statements and see if that's what you want. 

Super User
Posts: 11,552

Re: Changing a wide dataset to a long dataset

As a style note, unless you have a real good reason to record a "meeting" (and is that the start or end of the meeting) to the thousandth of a second I suggest ditching datetime and go to date values. I know there are lots of systems that are defaulting to datetime for anything involving dates but when the hours, minutes, seconds and fractions of a second are exactly the same for every variable then they are meaningless. Or at least assign a format like DTDATE9. so all the ugly repetive zeros do not appear in output.

Occasional Contributor
Posts: 19

Re: Changing a wide dataset to a long dataset

I definitely agree.  This data feeds in from a web app which defaults to this ridiculous datetime format, even though the time is typically all 0's.  Typically down the line I do change the format, or extract the day and month (since year is a column already).

Solution
‎08-22-2017 01:30 PM
Occasional Contributor
Posts: 19

Re: Changing a wide dataset to a long dataset

I was able to make PROC TRANSPOSE work using the following code:

 

PROC TRANSPOSE DATA=WORK.t_yoy
	OUT=flash.TRANSPOSED_yoy(LABEL="Transposed WORK.T2017")
	PREFIX=Text
	NAME=Section
	LABEL=Label
;
BY Emp_ID FiscalYear DBN;
	VAR ImprovementPlan ActionPlan TimeLinePlan SupportPlan AssessmentPlan;
	COPY DBN Emp_ID FiscalYear Meeting1 Meeting2 Meeting3 Meeting4 Meeting5;

RUN; QUIT;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 151 views
  • 4 likes
  • 4 in conversation