Data Manipulation question

[dennis_oz]

Hi all,

how can the below be achieved 

data have;
input Code1 :$20. Group :$20. Val1 Val2 Val3;
datalines;
A100 Big 90 45 25
A100 Medium 60 70 33
B100 Medium 60 70 33
B100 Small 60 70 33
C100 Big 60 34 45
C100 Medium 60 70 33
C100 Small 60 70 33
;
run;

Want
---
A100 Big 90 45 25
A100 Medium 60 70 33
A100 Small 0 0 0
B100 Big 0 0 0
B100 Medium 60 70 33
B100 Small 60 70 33
C100 Big 60 34 45
C100 Medium 60 70 33
C100 Small 60 70 33

[PGStats]

The rough way:

data want;
do until (last.code1);
	set have; by code1;
	select (group);
		when ("Big") do;
			b1=val1; b2=val2; b3=val3; end;
		when ("Medium") do;
			m1=val1; m2=val2; m3=val3; end;	
		when ("Small") do;
			s1=val1; s2=val2; s3=val3; end;
		otherwise;
		end;
	end;
group = "Big"; val1=coalesce(b1, 0); val2=coalesce(b2, 0); val3=coalesce(b3, 0); output;
group = "Medium"; val1=coalesce(m1, 0); val2=coalesce(m2, 0); val3=coalesce(m3, 0); output; 
group = "Small"; val1=coalesce(s1, 0); val2=coalesce(s2, 0); val3=coalesce(s3, 0); output;
keep code1 group val1 val2 val3;
run;

[PGStats · Accepted solution]

The SQL way:

proc sql;
create table want as
select 
	a.code1, b.group, 
	coalesce(c.val1,0) as val1,
	coalesce(c.val2,0) as val2,
	coalesce(c.val3,0) as val3
from
	(select unique code1 from have) as a cross join
	(select unique group from have) as b left join
	have as c 
		on a.code1=c.code1 and b.group=c.group
order by code1, group;
quit;

[hashman]

@dennis_oz:

To @PGStats' "rough way" and "SQL way", I would add a "mixed way":

data have ;                                                                                                                             
  input code :$20. group :$20. val1-val3 ;                                                                                              
  cards ;                                                                                                                               
A100  Big     90  45  25                                                                                                                
A100  Medium  60  70  33                                                                                                                
B100  Medium  60  70  33                                                                                                                
B100  Small   60  70  33                                                                                                                
C100  Big     60  34  45                                                                                                                
C100  Medium  60  70  33                                                                                                                
C100  Small   60  70  33                                                                                                                
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
proc sql ;                                                                                                                              
  create view exc as                                                                                                                    
  select distinct a.code, b.group from have a, have b                                                                                   
  except                                                                                                                                
  select distinct code, group from have                                                                                                 
  order 1, 2                                                                                                                            
  ;                                                                                                                                     
quit ;                                                                                                                                  
                                                                                                                                        
data want ;                                                                                                                             
  merge have (in=h) exc ;                                                                                                               
  by code group ;                                                                                                                       
  array v val: ;                                                                                                                        
  if not h then do over v ;                                                                                                             
    v = 0 ;                                                                                                                             
  end ;                                                                                                                                 
run ;                 

EDIT: On second thought, it is more efficient (and more concise) to do it this way (obviously, under the assumption that HAVE is sorted by [code,group]:

proc summary data = have completetypes nway ;                                                                                   
  class code group ;                                                                                                                    
  var val: ;                                                                                                                            
  output out = allgroups (drop = _:) nmiss= ;                                                                                             
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  merge allgroups have ;                                                                                                                
  by code group ;                                                                                                                       
run ;   

Kind regards

Paul D. 

[dennis_oz]

Hi, thanks for sending this .. it's awesome to see this requirement can be done in these many ways

[Jagadishkatam]

Alternatively please try

data have;
input Code1 :$20. Group :$20. Val1 Val2 Val3;
datalines;
A100 Big 90 45 25
A100 Medium 60 70 33
B100 Medium 60 70 33
B100 Small 60 70 33
C100 Big 60 34 45
C100 Medium 60 70 33
C100 Small 60 70 33
;
run;

proc freq data=have noprint;
table code1*group/out=dummy(drop=count percent)  sparse;
run;

proc sort data=have;
by code1 group;
run;

proc sort data=dummy;
by code1 group;
run;

data want;
merge have(in=a) dummy(in=b);
by code1 group;
if b;
if b and not a then do;
val1=0;
val2=0;
val3=0;
end;
run;

[dennis_oz]

Hi @Jagadishkatam, this works brilliantly as well .
thanks for showing that " Proc FREQ" can do this .