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:
DBN | Emp_ID | FiscalYear | Improvement_plan | Action_Plan | Timeline | Support_Plan | Assessment_Plan | Meeting1 | Meeting2 | Meeting3 | Meeting4 | Meeting5 |
07B025 | 469849 | 2015 | 1) 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.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
07B025 | 469849 | 2017 | 1E: 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 above | 1) 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.000 | 12JAN2017:00:00:00.000 | 25MAY2017:00:00:00.000 | . | . |
02Q512 | 964702 | 2015 | 1) 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.000 | 21JAN2015:00:00:00.000 | 27MAY2015:00:00:00.000 | . | . |
01M515 | 140817 | 2016 | 1) 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/learning | 21SEP2015:00:00:00.000 | 22APR2016:00:00:00.000 | 23JUN2016:00:00:00.000 | . | .
|
I need it to look like this:
DBN | Emp_ID | FiscalYear | Section | Text | Meeting1 | Meeting2 | Meeting3 | Meeting4 | Meeting5 | |||||
07B025 | 469849 | 2015 | Action_plan | 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) | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . | |||||
07B025 | 469849 | 2015 | Timeline | Refer to the timelines included at the end of each action step/ activity listed above. | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . | |||||
07B025 | 469849 | 2015 | Support_Plan | A Ramapo consultant will support you with differentiating instruction, pacing, and engaging students. | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . | |||||
07B025 | 469849 | 2015 | Assessment_Plan | In our second and third meetings, we will review the evidence from classroom observations and student work/learning | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015: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:
DBN | Emp_ID | FiscalYear | Section | Meeting1 | Meeting2 | Meeting3 | Meeting4 | Meeting5 |
07B025 | 469849 | 2015 | 1) Developing strategies for consistently monitoring student behavior, setting clear expectations for behavior, and routinizing procedures to minimize disruptions. | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
07B025 | 469849 | 2015 | 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) | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
07B025 | 469849 | 2015 | Refer to the timelines included at the end of each action step/ activity listed above. | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
07B025 | 469849 | 2015 | A Ramapo consultant will support you with differentiating instruction, pacing, and engaging students. | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
07B025 | 469849 | 2015 | In our second and third meetings, we will review the evidence from classroom observations and student work/learning | 12SEP2014:00:00:00.000 | 22JAN2015:00:00:00.000 | 28MAY2015:00:00:00.000 | . | . |
I appreciate any advice you have for me.
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;
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 ?
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:
DBN | Emp_ID | FiscalYear | Meeting1 | Meeting2 | Meeting3 | Meeting4 | Meeting5 | Source | Column1 | Column2 | Column3 | Column4 |
01M015 | 469759 | 2015 | 9/12/2014 | 1/22/2015 | 5/28/2015 | NULL | NULL | improvementplan | 69.2053 | 91.88742 | 106.2914 | 88.24503 |
01M015 | 469759 | 2017 | 9/16/2016 | 1/12/2017 | 5/25/2017 | NULL | NULL | actionplan | 597.351 | 539.0728 | 839.2384 | 678.3113 |
01M015 | 967302 | 2015 | 9/12/2014 | 1/21/2015 | 5/27/2015 | NULL | NULL | timelineplan | 14.23841 | 1.490066 | 14.07285 | 14.23841 |
01M015 | 108117 | 2016 | 9/21/2015 | 4/22/2016 | 6/23/2016 | NULL | NULL | supportplan | 282.6159 | 239.404 | 409.7682 | 220.5298 |
01M015 | 118900 | 2015 | 9/17/2014 | 1/16/2015 | 5/29/2015 | NULL | NULL | assessmentplan | 221.5232 | 205.4636 | 234.2715 | 437.2517 |
01M020 | 735789 | 2017 | 9/26/2016 | 3/28/2017 | NULL | 12/15/2016 | NULL | . | . | . | . | |
01M020 | 785214 | 2017 | 9/16/2016 | NULL | NULL | 11/4/2016 | 11/18/2016 | . | . | . | . | |
01M020 | 164976 | 2016 | 9/28/2015 | 4/21/2016 | 6/14/2016 | NULL | NULL | . | . | . | . |
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.
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.
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).
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.