Transposing Wide to Long

[cassylovescats]

Hello,

I am trying to transpose this data - I am struggling with the months because they have different characters:

libname Example "~/my_courses/Homework/FinalHomework";

data Sugar;
infile '~/my_courses/Homework/FinalHomework/CaneDataTestChi.csv/' dsd firstobs=2;
input DistrictGroup $ Direction $ Jul96 Aug96 Sep96 Oct96;
;
run;

proc print data=Sugar;

run;

To make it from:

DistrictGroup Direction Jul96 Aug96 Sep96 Oct96

Region1                S             1         2         3           4

Region1                E             5         6         7           8

Region2                E             9         10       11         12

Region3                N             13       14       15         16

To:

DistrictGroup Month Amount

Region1            Jul96       1

Region1            Jul96       5

Region2            Jul96       9

Region3            Jul96      13

Region1            Aug96      2

Region1            Aug96      6

Region2            Aug96      10

Region3            Aug96      14

...

Thank you!

[r_behata · Accepted solution]

data have;
input DistrictGroup $ Jul96 Aug96 Sep96 Oct96;
cards;
Region1 1 2 3 4
Region1 5 6 7 8
Region2 9 10 11 12
Region3 13 14 15 16
;
run;

data want;	
	set have;

	array mon{*} _numeric_;

	do _n_=1 to dim(mon);
		month=vname(mon[_n_]);
		amount=mon[_n_];
		output;
	end;

	drop Jul96 Aug96 Sep96 Oct96;
run;

[art297]

You've already marked this as correct, and there's no need to change that, but I think you'll have to make month a date variable if you want the file to be sorted in the order you desire. E.g.:

data have;
  input DistrictGroup $ Jul96 Aug96 Sep96 Oct96;
  cards;
Region1             1         2         3           4
Region1             5         6         7           8
Region2             9         10       11         12
Region3             13       14       15         16
;

data want (keep=DistrictGroup Month Amount);
  set have;
  format Month monyy5.;
  array mon{*} Jul96--Oct96;
  do _n_=1 to dim(mon);
    Month=input(vname(mon[_n_]),monyy5.);
    Amount=mon[_n_];
    output;
  end;
run;

proc sort data=want;
  by Month;
run;

Art, CEO, AnalystFinder.com

[novinosrin]

data have;
input DistrictGroup $ Jul96 Aug96 Sep96 Oct96;
cards;
Region1             1         2         3           4
Region1             5         6         7           8
Region2             9         10       11         12
Region3             13       14       15         16
;


data want;
 do _iorc_= 1 by 1 until(last.DistrictGroup);
  set have end=z ;
  by DistrictGroup;
  array t(99,999,999) _temporary_;
  array nm(99999)$ _temporary_;
  array j jul96--oct96;
  do over j;
  t(_n_,_iorc_,_i_)=j;
  end;
 end;
 nm(_n_)=DistrictGroup;
 if z;
 do over j;
  do k=1 to 999;
   do _n_=1 to dim(nm) while(nm(_n_)>' ');
   DistrictGroup=nm(_n_);
   month=vname(j);
   Amount=t(_n_,k,_i_);
   if Amount then output;
   end;
  end;
 end;
 keep Districtgroup month amount;
run;