delete and cells up

[hx]

Hi, I have a dataset which looks like

years	group	t
2016	aas	2
2016	dsa	1
2016	asdfg	.
2015	aas	3
2015	dsa	3
2015	asdfg	6
2014	aas	5
2014	dsa	76
2014	asdfg	765
2013	aas	.
2013	dsa	2
2013	asdfg	.
2012	aas	3
2012	dsa	3
2012	asdfg	.
2011	aas	.
2011	dsa	.
2011	asdfg	1
2010	aas	3
2010	dsa	3
2010	asdfg	4
2009	aas	6
2009	dsa	8
2009	asdfg	9

And what I want will be like 

years	group	t	t1	t2	t3	t4	t5	t6	t7
2016	aas	2	3	5	.	3	.	3	6
2016	dsa	1	3	76	2	3	.	3	8
2016	asdfg	.	6	765	.	.	1	4	9
2015	aas	3	5	.	3	.	3	6
2015	dsa	3	76	2	3	.	3	8
2015	asdfg	6	765	.	.	1	4	9
2014	aas	5	.	3	.	3	6
2014	dsa	76	2	3	.	3	8
2014	asdfg	765	.	.	1	4	9
2013	aas	.	3	.	3	6
2013	dsa	2	3	.	3	8
2013	asdfg	.	.	1	4	9
2012	aas	3	.	3	6
2012	dsa	3	.	3	8
2012	asdfg	.	1	4	9
2011	aas	.	3	6
2011	dsa	.	3	8
2011	asdfg	1	4	9
2010	aas	3	6
2010	dsa	3	8
2010	asdfg	4	9
2009	aas	6
2009	dsa	8
2009	asdfg	9

Basically, I want to generate t-sequence variables that have values one year before. For example, t1 is generated with the start of values from year 2015. t2 is generated with the start of values from year 2014 and so on.

I know it is quite simple to do with excel, I can delete and cells up one column by another.But the truth is the original dataset contains 500,000 observations. I am trying to figure out a way using SAS. Can some one help me out?

really appreciated,

[art297]

Fairly simple in SAS as well. e.g.:

proc sort data=have;
  by group descending years;
run;

proc transpose data=have out=want (drop=_name_);
  var t;
  by group;
  id years;
run;

data want (drop=_:);
  format years 4.;
  set want;
  array yeardata(*) _2016-_2009;
  array want(8) t t1-t7;
  years=2017;
  do _i=1 to 8;
    call missing(of want(*));
    years=years-1;
    _j=0;
    do _k=_i to 8;
      _j+1;
      want(_j)=yeardata(_k);
    end;
    output;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input years	group $	t;
cards;
2016	aas	2
2016	dsa	1
2016	asdfg	.
2015	aas	3
2015	dsa	3
2015	asdfg	6
2014	aas	5
2014	dsa	76
2014	asdfg	765
2013	aas	.
2013	dsa	2
2013	asdfg	.
2012	aas	3
2012	dsa	3
2012	asdfg	.
2011	aas	.
2011	dsa	.
2011	asdfg	1
2010	aas	3
2010	dsa	3
2010	asdfg	4
2009	aas	6
2009	dsa	8
2009	asdfg	9
;
run;
proc sort data=have;
 by group descending years;
run;
proc transpose data=have out=temp(drop=_name_) prefix=t_;
 by group;
 var t;
 id years;
run;
proc sql noprint;
select max(years),min(years) into : max separated by ' ',
       : min separated by ' ' from have;
quit;
options mprint mlogic symbolgen;
%macro combine;
 %do i=&max %to &min %by -1;
  data x;
   year=&i;
   set temp(keep=group t_&min-t_&i rename=(t_&min-t_&i=t%eval(&max-&i+&min)-t&max));
  run;
  proc append base=want data=x force nowarn;run;
 %end;
%mend;

%combine

proc print noobs;run;