Hi!
I have a test data set. If there is more than one identifier in the table and it is included 20900101 date then keep it that row. The rest are related to this ids delete it/drop it.
data;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2
1 21000101 rte www
4 20190101 www rrr
;
run;
so i'd like to this: (1. id contains 20900101 2. id contains 20900101 3. id contains 20900101 4. id not contains so keep it)
data;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20900101 jdk lao2
3 20900101 wer wes2
4 20190101 www rrr
;
run;
Thanks!
It looks like a typical job for the DoW-loop:
data test ;
input id (date var1 var2) ($) ;
cards ;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2
1 21000101 rte www
4 20190101 www rrr
;
run ;
proc sort data = test out = _test ;
by id ;
run ;
data want (drop = _:) ;
do _n_ = 1 by 1 until (last.id) ;
set _test ;
by id ;
if date ne "20900101" then continue ;
output ;
_flag = 1 ;
end ;
do _n_ = 1 to _n_ ;
set _test ;
if not _flag then output ;
end ;
run ;
Kind regards
Paul D.
It looks like a typical job for the DoW-loop:
data test ;
input id (date var1 var2) ($) ;
cards ;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2
1 21000101 rte www
4 20190101 www rrr
;
run ;
proc sort data = test out = _test ;
by id ;
run ;
data want (drop = _:) ;
do _n_ = 1 by 1 until (last.id) ;
set _test ;
by id ;
if date ne "20900101" then continue ;
output ;
_flag = 1 ;
end ;
do _n_ = 1 to _n_ ;
set _test ;
if not _flag then output ;
end ;
run ;
Kind regards
Paul D.
Thanks for your quick help!
data have;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2
1 21000101 rte www
4 20190101 www rrr
;
run;
proc sql;
create table want as
select *
from (select * from have group by id having count(*)>1)
where date='20900101'
union all
select * from have group by id having count(*)=1;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: