BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hx
Calcite | Level 5 hx
Calcite | Level 5

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

2 REPLIES 2
art297
Opal | Level 21

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
Super User
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;





sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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