BookmarkSubscribeRSS Feed
Vk_2
Obsidian | Level 7
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

2 REPLIES 2
Vk_2
Obsidian | Level 7
It is not duplicate, I just used the same dataset. But I want an indication before removing the record indicating two different type being mentioned(BIG&SMALL)

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 522 views
  • 0 likes
  • 2 in conversation