DATA Step, Macro, Functions and more

delete and cells up

Accepted Solution Solved
Reply
Occasional Contributor hx
Occasional Contributor
Posts: 17
Accepted Solution

delete and cells up

Hi, I have a dataset which looks like

yearsgroupt
2016aas2
2016dsa1
2016asdfg.
2015aas3
2015dsa3
2015asdfg6
2014aas5
2014dsa76
2014asdfg765
2013aas.
2013dsa2
2013asdfg.
2012aas3
2012dsa3
2012asdfg.
2011aas.
2011dsa.
2011asdfg1
2010aas3
2010dsa3
2010asdfg4
2009aas6
2009dsa8
2009asdfg9

And what I want will be like 

yearsgrouptt1t2t3t4t5t6t7
2016aas235.3.36
2016dsa137623.38
2016asdfg.6765..149
2015aas35.3.36 
2015dsa37623.38 
2015asdfg6765..149 
2014aas5.3.36  
2014dsa7623.38  
2014asdfg765..149  
2013aas.3.36   
2013dsa23.38   
2013asdfg..149   
2012aas3.36    
2012dsa3.38    
2012asdfg.149    
2011aas.36     
2011dsa.38     
2011asdfg149     
2010aas36      
2010dsa38      
2010asdfg49      
2009aas6       
2009dsa8       
2009asdfg9       

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: 9,681

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;





View solution in original post


All Replies
PROC Star
Posts: 7,363

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: 9,681

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 125 views
  • 1 like
  • 3 in conversation