row-wise proc freq of non empty variables

[NewUsrStat]

Hi guys, 

suppose to have the following table: 

   D1_1              D1_2           D1_3             D1_4              D1_5           

 yellow           blue            

  pink             yellow         magenta                           black          

magenta                            orange           green

green              magenta     pink               white                   

Is there a way to get row-wise the the frequency of non empty variables? 

let's say: 

row1:  2 

row2: 4

row3: 3

row4: 4

Moreover, should proc freq give me the overall frequencies of all words of the entire table although under different columns? Should I transpose the table? 

Desired output: 

yellow: 2

blue: 1

pink: 2

magenta: 2

black: 1

orange: 1

green: 2

white: 1

Thank you in advance

[PaigeMiller · Accepted solution]

For the row-wise analysis, you can use the CMISS() function in a SAS data step, something like this:

freq_of_non_miss = 5 - cmiss(of d1_1-d1_5);

For the second question, you would need to re-arrange the data in order to have the correct answer from PROC FREQ

data re_arrange;
    set have;
    array d d1_1-d1_5;
    do i=1 to 5;
        if not missing(d(i)) then do;
             value=d(i);
             output;
         end;
    end;
    drop i d1_1-d1_5;
run;

[NewUsrStat]

Thank you very much!

[SASJedi]

Because each variable contains only one word, in a DATA step you could concatenate them and count the number of words like this:

data have;
  infile datalines dsd dlm='|' truncover;
  input D1_1:$10. D1_2:$10. D1_3:$10. D1_4:$10. D1_5:$10.;
datalines; 
yellow|blue|
pink|yellow|magenta||black
magenta|orange|green
green|magenta|pink|white
;

For the frequency count by word, it's easiest to transpose with a DATA step first and then use good old PROC FREQ:

data forFreq (keep=word);
	set have;
	array w[*] D:;
	do i=1 to dim(w);
		word=w[i];
		if not cmiss(word) then output;
	end;
run;

proc freq data=forFreq;
	tables word/nocum nopercent;
run;