BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Lapis Lazuli | Level 10

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
NewUsrStat
Lapis Lazuli | Level 10
Thank you very much!
SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 794 views
  • 3 likes
  • 3 in conversation