DATA table1;
infile datalines DELIMITER=',';
INFORMAT id 2. type $10. date date2 MMDDYY10. ;
INPUT id date type date2;
format date date9.
date2 date9.;
DATALINES;
1,02/09/2012,BIG,02/09/2012
2,05/16/2012,BIG,05/18/2012
2,06/18/2012,BIG,06/18/2012
2,06/18/2012,SMALL,
3,08/08/2011,BIG,08/08/2012
3,09/13/2011,BIG,09/13/2012
4,06/08/2016,BIG,06/12/2016
4,06/10/2016,SMALL,
5,08/16/2012,BIG,08/16/2012
5,08/15/2012,SMALL,
;
run;
/*removing same date for an id with different type- only the record with SMALL is REMOVED*/
proc sql;
create table comb as
Select id, date, type,date2 from table1 t
Where type <> "SMALL" or
not exists(select date from table1
where id = t.id and date = t.date and type <> "SMALL");
run;
This gives:
id date type date2
1 09FEB2012 BIG 09FEB2012
2 16MAY2012 BIG 18MAY2012
2 18JUN2012 BIG 18JUN2012
3 08AUG2011 BIG 08AUG2012
3 13SEP2011 BIG 13SEP2012
4 08JUN2016 BIG 12JUN2016
4 10JUN2016 SMALL .
5 15AUG2012 SMALL .
5 16AUG2012 BIG 16AUG2012
this removed the record that I want (2 06/18/2012 SMALL )but I want a field name cat for the same date and id to include BIG & SMALL
like this
id date type date2 cat
1 09FEB2012 BIG 09FEB2012
2 16MAY2012 BIG 18MAY2012
2 18JUN2012 BIG 18JUN2012 BIG & SMALL
3 08AUG2011 BIG 08AUG2012
3 13SEP2011 BIG 13SEP2012
4 08JUN2016 BIG 12JUN2016
4 10JUN2016 SMALL .
5 15AUG2012 SMALL .
5 16AUG2012 BIG 16AUG2012
