restructure one table

[windyboo]

Hi,

I want to restructure data in one table.

My table is:

Year	LenderID	LenderID2	counts	CodePaire
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
...	...	...	..	...

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:

LenderID	LenderID2	counts	CodePaire	LenderID	LenderID2	counts	CodePaire	LenderID	LenderID2	counts	CodePaire
1995	-	-	-	-	-	-	-	-	-	-	-	-	…
1996	-	-	-	-	-	-	-	-	-	-	-	-	…
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	…
2004	-	-	-	-	-	-	-	-	-	-	-	-	…
2005	-	-	-	-	-	-	-	-	7858	7827	4	36	…
2006	-	-	-	-	-	-	-	-	7858	7827	6	36	…
2007	-	-	-	-	113442	7140	1	33	7858	7827	4	36	…
2008	-	-	-	-	-	-	-	-	7858	7827	3	36	…
2009	-	-	-	-	-	-	-	-	-	-	-	-	…
2010	-	-	-	-	-	-	-	-	-	-	-	-	…
2011	30309	7135	1	11	-	-	-	-	7858	7827	3	36	…
2012	-	-	-	-	-	-	-	-	7858	7827	2	36	…

It seems strange but that would facilitate manipulations.

Thank you.

[data_null__ · Accepted solution]

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;