I have a dataset that looks like this:
ID var1
1 A
1 B
1
2
3
4 A
4
I want it to look like this:
ID var1
1 A
1 B
2
3
4 A
I.e. I want to delete the blanks within each level of the ID variable, only if there is another observation within that level that is a non-blank?
Thank you
data have;
infile cards truncover;
input ID var1 $;
cards;
1 A
1 B
1
2
3
4 A
4
;
proc sql;
create table want as
select *
from have
group by id
having n(var1) and var1>' ' or not n(var1);
quit;
Give this a try.
Tom
data Have;
length ID 8 var1 $8;
infile cards missover;
input ID var1;
cards;
1 A
1 B
1
2
3
4 A
4
run;
proc sort data=Have out=Inter1;
by ID;
run;
data Inter2;
retain DelFlag;
set Inter1;
by ID;
keep ID DelFlag;
if first.ID then
DelFlag = 0;
if ^missing(var1) then
DelFlag = 1;
if last.ID then
output;
run;
proc sql noprint;
create table Inter3 as
select i1.*, i2.DelFlag
from Inter1 i1 inner join Inter2 i2 on(i1.ID = i2.ID);
quit;
data Want;
set Inter3;
drop DelFlag;
if missing(var1) & DelFlag then
delete;
run;
Here another coding option. Assumes your data is sorted by ID.
data have;
infile cards truncover;
input ID var1 $;
datalines;
1 A
1 B
1
2
2
3
4 A
4
;
data want;
merge
have(in=ina where=(missing(var1)) )
have(in=inb where=(not missing(var1)) )
;
by id;
if first.id or inb then output;
run;
Nice thinking Sir @Patrick If it were sorted by ID Var1
I might go for
data have;
infile cards truncover;
input ID var1 $;
cards;
1 A
1 B
1
2
3
4 A
4
;
proc sort data=have out=have1;
by id var1;
run;
data want;
set have1;
by id;
if first.id and last.id or var1>' ';
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.