CODEWITHSUNDEEP
sas
LMGagne
LMGagne

Reputation: 1706

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

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.

Upvotes: 0

Views: 82

Answers (2)

LMGagne
LMGagne

Reputation: 1706

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;

Upvotes: 0

user2877959
user2877959

Reputation: 1792

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;

Upvotes: 1

Related Questions