restructure one table

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

restructure one table

Hi,

I want to restructure data in one table.

My table is:

YearLenderIDLenderID2countsCodePaire
2011303097135111
20071134427140133
199778587827336
199878587827436
199978587827336
200078587827336
200178587827436
200278587827236
200378587827136
200578587827436
200678587827636
200778587827436
200878587827336
201178587827336
201278587827236
201096477827137
1997219767827141
1999219767827241
2000219767827141
2001219767827241
2002219767827241
2003219767827241
2006219767827241
2008219767827241
2009219767827341
2010219767827441
..............

I have data from 1995 to 2012. There are all pairs of two LenderIDs (LenderID and LenderID2) which exist linked to the function count executed. Some years are not there in pairs of two LenderIDs.

I would like to move data in a way that the first column is year from 1995 to 2012. The four next columns will be repeated on the line linked to the right year. Take a look what it will look like:

LenderIDLenderID2countsCodePaireLenderIDLenderID2countsCodePaireLenderIDLenderID2countsCodePaire
1995------------
1996------------
1997--------78587827336
1998--------78587827436
1999--------78587827336
2000--------78587827336
2001--------78587827436
2002--------78587827236
2003--------78587827136
2004------------
2005--------78587827436
2006--------78587827636
2007----113442714013378587827436
2008--------78587827336
2009------------
2010------------
2011303097135111----78587827336
2012--------78587827236

It seems strange but that would facilitate manipulations.

Thank you.


Accepted Solutions
Solution
‎01-31-2014 12:40 PM
Respected Advisor
Posts: 3,799

Re: restructure one table

I reckon something like this might work.  Helps that your variables are all the same type (or are they?).  Give it study see what you think..

data code;
   input Year LenderID LenderID2 counts CodePair;
   cards;
2011 30309 7135 1 11
2007 113442 7140 1 33
1997 7858 7827 3 36
1998 7858 7827 4 36
1999 7858 7827 3 36
2000 7858 7827 3 36
2001 7858 7827 4 36
2002 7858 7827 2 36
2003 7858 7827 1 36
2005 7858 7827 4 36
2006 7858 7827 6 36
2007 7858 7827 4 36
2008 7858 7827 3 36
2011 7858 7827 3 36
2012 7858 7827 2 36
2010 9647 7827 1 37
1997 21976 7827 1 41
1999 21976 7827 2 41
2000 21976 7827 1 41
2001 21976 7827 2 41
2002 21976 7827 2 41
2003 21976 7827 2 41
2006 21976 7827 2 41
2008 21976 7827 2 41
2009 21976 7827 3 41
2010 21976 7827 4 41
;;;;
   run;
proc summary nway completetypes;
  
class year codepair;
   output out=expand(drop=_type_ _freq_ codepair rename=(code=codepair))
     
idgroup(out(L: counts)=) idgroup(out(codepair)=code);
   label Year='Year' LenderID='LenderID' LenderID2='LenderID2' counts='Counts';
  
attrib Code: label='Codepair';
  
run;
data expand;
   set expand;
   by year;
   if first.year then index=0;
   index +
1;
  
run;
proc transpose out=tall;
   by year index;
   var LenderID LenderID2 counts CodePair;
   run;
proc transpose out=wide(drop=_name_) delimiter=_;
   by year;
   id _name_ index;
   idlabel _name_;
   var col1;
   run;
proc print;
proc print label;
  
run;

View solution in original post


All Replies
Solution
‎01-31-2014 12:40 PM
Respected Advisor
Posts: 3,799

Re: restructure one table

I reckon something like this might work.  Helps that your variables are all the same type (or are they?).  Give it study see what you think..

data code;
   input Year LenderID LenderID2 counts CodePair;
   cards;
2011 30309 7135 1 11
2007 113442 7140 1 33
1997 7858 7827 3 36
1998 7858 7827 4 36
1999 7858 7827 3 36
2000 7858 7827 3 36
2001 7858 7827 4 36
2002 7858 7827 2 36
2003 7858 7827 1 36
2005 7858 7827 4 36
2006 7858 7827 6 36
2007 7858 7827 4 36
2008 7858 7827 3 36
2011 7858 7827 3 36
2012 7858 7827 2 36
2010 9647 7827 1 37
1997 21976 7827 1 41
1999 21976 7827 2 41
2000 21976 7827 1 41
2001 21976 7827 2 41
2002 21976 7827 2 41
2003 21976 7827 2 41
2006 21976 7827 2 41
2008 21976 7827 2 41
2009 21976 7827 3 41
2010 21976 7827 4 41
;;;;
   run;
proc summary nway completetypes;
  
class year codepair;
   output out=expand(drop=_type_ _freq_ codepair rename=(code=codepair))
     
idgroup(out(L: counts)=) idgroup(out(codepair)=code);
   label Year='Year' LenderID='LenderID' LenderID2='LenderID2' counts='Counts';
  
attrib Code: label='Codepair';
  
run;
data expand;
   set expand;
   by year;
   if first.year then index=0;
   index +
1;
  
run;
proc transpose out=tall;
   by year index;
   var LenderID LenderID2 counts CodePair;
   run;
proc transpose out=wide(drop=_name_) delimiter=_;
   by year;
   id _name_ index;
   idlabel _name_;
   var col1;
   run;
proc print;
proc print label;
  
run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 174 views
  • 0 likes
  • 2 in conversation