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.
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..
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..
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.