topic Transposing Wide to Long in SAS Studio

Transposing Wide to Long

cassylovescats — Wed, 20 Nov 2019 21:56:58 GMT

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!

Re: Transposing Wide to Long

r_behata — Wed, 20 Nov 2019 22:03:12 GMT

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;

Re: Transposing Wide to Long

art297 — Wed, 20 Nov 2019 22:27:07 GMT

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

Re: Transposing Wide to Long

novinosrin — Thu, 21 Nov 2019 01:18:53 GMT


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;