When running PROC TRANSPOSE on a wide dataset that is sparse, it would be nice if the output could be filtered so rows with zero values, e.g., are excluded.
This sample does what I want, but I'd like to add something like an "if value <> 0" or "where value ne 0" to the proc transpose, so the output is limited to rows where value is not zero. Otherwise the resulting output will be huge, especially if I have about 50 "key" columns in the input and millions of records to transpose.
data sample_data;
input id $ a b c d e f g;
datalines;
01 1 0 0 0 0 0 0
02 0 1 0 0 0 0 0
03 0 0 -1 0 0 0 0
04 0 3 0 0 0 0 0
05 0 0 0 0 0 0 5
;
proc sort data = sample_data;
by id a b c d e f g;
run;
proc transpose data = sample_data
out = sample_transpose(rename=(_NAME_=key
COL1=value));
var a b c d e f g;
by id;
run;