Changing a wide data set to a long data set in SAS

0
votes

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:

enter image description here

I need it to look like this: enter image description here

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: enter image description here

I appreciate any advice you have for me.

2
sas
First, you should post your actual data so someone can copy and paste it into a sas program. That said, what you want to do can easily be done using proc transpose. Post your data and I'll provide a workable solution – DCR

2 Answers

1
votes

union in proc SQL should do the trick

proc sql;
  create table want as
  select DBN, Emp_ID, FiscalYear, 'Action_Plan' as Section, Action_Plan as Text, Meeting1, Meeting2, Meeting3, Meeting4, Meeting5
  from have
  union
  select DBN, Emp_ID, FiscalYear, 'Timeline' as Section, Timeline as Text, Meeting1, Meeting2, Meeting3, Meeting4, Meeting5
  from have
  union
  select DBN, Emp_ID, FiscalYear, 'Support_Plan' as Section, Support_Plan as Text, Meeting1, Meeting2, Meeting3, Meeting4, Meeting5
  from have
  union
  select DBN, Emp_ID, FiscalYear, 'Assessment_Plan' as Section, Assessment_Plan as Text, Meeting1, Meeting2, Meeting3, Meeting4, Meeting5
  from have
  ;
quit;

SAS also has proc transpose to do that kind of operation.

EDIT: something in the lines of

proc sort data=have;
  by DBN Emp_ID FiscalYear Meeting1 Meeting2 Meeting3 Meeting4 Meeting5;
run;    
proc transpose data=have out=want(rename=(column1=Text)) name=Section prefix=column;
  by DBN Emp_ID FiscalYear Meeting1 Meeting2 Meeting3 Meeting4 Meeting5;
  var action_plan timeline support_plan assessment_plan;
run;
0
votes

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

PROC TRANSPOSE DATA=WORK.t_yoy
    OUT=flash.TTRANSPOSED_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;