## delete and cells up

Solved
Occasional Contributor
Posts: 19

# delete and cells up

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,

Accepted Solutions
Solution
‎03-02-2017 02:38 PM
Super User
Posts: 10,784

## Re: delete and cells up

``````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;

``````

All Replies
PROC Star
Posts: 8,164

## Re: delete and cells up

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

Solution
‎03-02-2017 02:38 PM
Super User
Posts: 10,784

## Re: delete and cells up

``````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;

``````
☑ This topic is solved.