I created a dummy table using the data provided, and added an ID column, as a unique value for identifying each survey taker aligning with their answer values. DATA WORK.Have;
FORMAT ID $5. Fave_Colors $10. Fave_Seasons $10. Fave_Songs $10.;
INFORMAT ID $5. Fave_Colors $10. Fave_Seasons $10. Fave_Songs $10.;
INPUT ID Fave_Colors Fave_Seasons Fave_Songs;
INFILE DATALINES DLM=':' DSD;
DATALINES;
12345:2,8:4:2,4,6
23456:1,3,7,9:1,3:1,2
34567:4,9:1,4:6
45678:1,8:2,3:3,7,8
56789:2:1:8,9
;
DATA WORK.WANT (KEEP= ID Fave_Color);
SET WORK.HAVE;
ValueCt=COUNTW(Fave_Colors,',');
DO i=1 TO ValueCt;
Fave_Color=SCAN(Fave_Colors,i,',');
OUTPUT;
END;
RUN;
PROC FREQ DATA=WORK.WANT; TABLES Fave_Color /LIST MISSING NOPERCENT NOCUM; RUN; This will print a frequency that appears the way that you requested. If you require this to be an actual table versus just a printed frequency, just add the "OUT=" option after 'nocum' and fill in the name that you want for the new table with frequency details. The only difference between my frequency and the one in your post is that, yours has rows for color values that were not selected by anyone. So color option '5' and '6'. Since none of the survey takers selected those rows, when you run a proc freq against the realigned data, they will not appear on the output. Hope this helps.
... View more
