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..
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.