topic Re: How to delete blanks within a by group if there is at least one non-blank in that by group? in SAS Data Management

How to delete blanks within a by group if there is at least one non-blank in that by group?

KPCklebspn — Sat, 23 Nov 2019 21:38:30 GMT

I have a dataset that looks like this:

ID var1

1 A

1 B

4 A

I want it to look like this:

ID var1

1 A

1 B

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

Re: How to delete blanks within a by group if there is at least one non-blank in that by group?

novinosrin — Sat, 23 Nov 2019 22:07:32 GMT

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;

Re: How to delete blanks within a by group if there is at least one non-blank in that by group?

TomKari — Sat, 23 Nov 2019 22:10:38 GMT

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;

Re: How to delete blanks within a by group if there is at least one non-blank in that by group?

Patrick — Sun, 24 Nov 2019 00:33:37 GMT

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;

Re: How to delete blanks within a by group if there is at least one non-blank in that by group?

novinosrin — Sun, 24 Nov 2019 00:50:37 GMT

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;