BookmarkSubscribeRSS Feed
KPCklebspn
Obsidian | Level 7

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

4 REPLIES 4
novinosrin
Tourmaline | Level 20
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;
TomKari
Onyx | Level 15

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;
Patrick
Opal | Level 21

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;
novinosrin
Tourmaline | Level 20

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 826 views
  • 2 likes
  • 4 in conversation