My data set,OldTable, has 6 numeric variables ID (identity number), YEAR, N1-N4.
An identity(ID) can have several records each YEAR.
In one(1) new data set I want, for each year, an individuals all records if the individual has at least one record, for that year, where the value for N1 equals 1.
Ex.(for a single identity)
OldTable
ID_YEAR_ N1_ N2 _N3_ N4
11_2008__0__40__30__90
11_2008__1__30__35__50
11_2008__1__20__25__60
11_2009__0__30__50__95
11_2009__0__40__30__60
11_2010__1__20__50__40
11_2010__0__50__30__50
NewTable
ID_YEAR_ N1_ N2 _N3_ N4
11_2008__0__40__30__90
11_2008__1__30__35__50
11_2008__1__20__25__60
11_2010__1__20__50__40
11_2010__0__50__30__50
I know how to do it, if I didn't have to regard the different years:
select * from oldtable where id in (select distinct id from oldtable where n1=1);
If it's possible I would prefer not to use a macro processing different years.