SAS - Ensure table has all categories

sas_ensure_table_complete_categories.sas

/*This example goes over how to create a data set with all values. In this case item_id should have values 1 through 4 for every record
Missing values are included for the missing resp

Question originally from here: https://communities.sas.com/t5/SAS-Programming/How-to-format-this-data/m-p/665054

*/
*fake data;
data have;
input item_id $ resp;
datalines;
pp1111  1
pp1111  2
pp1111  3
pp2222  1
pp2222  2
pp2222  3
pp2222  4
pp3333  1
pp3333  3
pp3333  4
pp4444  1
pp4444  2
pp5555  1
;

*create sparse output, SAS fills in 0;
proc freq data=have noprint;
table item_id * resp / sparse out=temp;
run;

*anything 0, remove as per initial request - not REQUIRED;
data want;
set temp;
*removes resp from records that have a 0 count;
if count = 0 then call missing(resp);

drop count percent;
run;


/*SQL approach*/
*build skeleton table;
proc sql;
create table skeletonTable as
select distinct t1.item_id, t2.resp 
from have as t1, have as t2;
quit;

*merge with table (prior step combined);
proc sql;
create table want as
select t4.item_id, t3.resp
from have as t3 right join 
(select distinct t1.item_id, t2.resp 
from have as t1, have as t2) as t4
on t3.item_id=t4.item_id and t3.resp=t4.resp;
quit;