Column was summary

[mohdfaisal89]

Please help !

I have the below table in SAS

RECORD	FIRST_PASS	SECOND_PASS	THIRD_PASS	FOURTH_PASS
1234	R1	R3	R4	R5
5678	R3	R4	R5
9101	R3	R4	R5
1112	R2	R3	R4	R5

I need to summarize as below

SUMMARY	COUNT
R1	1
R2	1
R3	4
R4	4
R5	4

Note -

- R1, R2 are reason for pass.

- Pass Reason appears only once for each record

[Kurt_Bremser]

You can't have such a table, a variable name that starts with a digit is invalid.

The basic solution for such a problem is transpose and freq. For code examples, post data in usable form (data step with datalines).

[mohdfaisal89]

That table was just for illustration purpose. Please ignore the name convention. For understanding purpose, i wrote it as 1st .. 2nd..

Can i work on an array function and count respective outcome from the array?

[Kurt_Bremser]

You need to transpose to a vertical format first, then it's a breeze with proc freq:

proc transpose data=have out=trans;
var pass1 pass2 pass3 pass4;
run;

proc freq data=trans;
tables col1;
run;

Untested, for lack of data.

[mohdfaisal89]

PROC SQL;

CREATE TABLE F1 AS
select RECORD, FRIST_PASS from F1 WHERE FRIST_PASS IS NOT NULL UNION ALL 
select RECORD, SECOND_PASS from F1 WHERE SECOND_PASS IS NOT NULL UNION ALL 
select RECORD, THIRD_PASS from F1 WHERE THIRD_PASS IS NOT NULL UNION ALL 
select RECORD, FOURTH_PASS from F1 WHERE FOURTH_PASS IS NOT NULL;

QUIT;

Proc Freq data=F1;
tables First_pass; run;

This worked for me !

[yabwon · Accepted solution]

Hi,

I think datastep + hash table can do  what you need.

Bart

data have;                                                                                              
 input id p1 $ p2 $ p3 $ p4 $; 
cards4;                                                                                                 
1234 R1 R3 R4 R5                                                                                        
5678 R3 R4 R5 .                                                                                         
9101 R3 R4 R5 .                                                                                         
1112 R2 R3 R4 R5                                                                                        
;;;;                                                                                                    
run;

data _null_;
  length summary $ 8 count 8;
  declare hash H(ordered:"A");
  H.defineKey("summary");
  H.defineData("summary", "count");
  H.defineDone();
  call missing(summary, count);

  do until(eof);
    set have end = eof;
    array A p1-p4;

    do over A;
      summary = a;
      if H.find() then 
        do;
          count = 1;
          _iorc_ = H.add();
        end;
      else
        do;
          count + 1;
          _iorc_ = H.replace();
        end;
    end;
  end;

  _iorc_ = H.output(dataset:"want(where=(summary is not null))");
  stop;
run; 

[mohdfaisal89]

This is exactly what i was working on.. Thanks Mate !