Here is the sample data.I want to group together id's together based
on the value of id2 and finally have an idnew which will have the value
of the latest year's id as the final id value.I have tried to
illustrate with an example below.I have just shown the grouping for
one case it has to be the same everywhere and the id pertaining to the
latest year should be populated elsewhere (like 93693 is the id for
2003 that is why the new id has this value everywhere).Hope I will get
answers to this unique problem.
A DATA step will work here - after sorting your data file first in sequence using a BY variable list of your choice.
To "RETAIN" a running list of related variable values, you must declare a new SAS character variable of suitable "LENGTH" and then in the DATA step, use SAS FIRST. (the last variable in your BY list) to initialize your value-list variable and also LAST. to output your new SAS file/observation.
Since you must concatenate each related input observation to your "running list" variable, you will want to use a DATA step function like CATT / CATX / TRIM as you build your string of values.
The SAS support http://support.sas.com/ website has SAS-hosted documentation (HTML-format and PDF documents) and supplemental technical / conference papers. Also you can use the SEARCH facility to locate a related topic-oriented paper on the subject. A few are provided below for reference and review.
For what it's worth, here is a Google advanced search argument which can help you locate documents and other references on the SAS.COM domain/site:
Please read the post carefully it is beyond retaining a value.I want to co-relate the id based on a old id value and then group them together and then finally have a variable which will have the latest years value as its value through out.
Your description mentions "id2" but there is no such variable/column in your input or output/final data. Focus on explaining with SAS variable names as you have provided what should be input data and output results. I still believe that if you do some reading on DATA step programming and the references provided, it may help with your personal SAS programming development to learn and compose a SAS solution to your problem. Ideally this is a forum for learning, not just getting answers from others in the form of program code.