I have come up with a solution. This may not be the most elegant way but it works. Please feel free to provide any improvement. data have;
infile datalines dsd;
input Application_ID Product_ID $ ID $ Product1_Category $ Product2_Category $;
datalines;
11111,a,1,m1,m3
11111,b,2,n2,n4
11111,,3,k3,k5
11111,,4,t3,t7
;
run;
proc transpose data= Have out=Want(drop=_NAME_) Prefix=Prod_;
by Application_id;
var Product_ID;
copy Product1_Category Product2_Category;
id ID;
run;
data want(drop = Prod_1 - Prod_4 prodtemp_1-prodtemp_4 product1_category product2_category i);
set want;
by Application_ID;
retain prodtemp_1-prodtemp_4;
array prodtemp {4} $ prodtemp_1-prodtemp_4;
array prod {4} $ prod_1 - prod_4;
do i = 1 to 4;
if prod{i} ne '' then prodtemp{i} = prod{i};
prod{i} = prodtemp{i};
end;
array category {2} $ Product1_category Product2_category;
do i = 1 to 2;
if category {i} ='' then leave;
product_category = category{i};
product = prod{i};
output;
end;
run;
... View more