BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mohdfaisal89
Calcite | Level 5

Please help !

 

I have the below table in SAS

 

RECORDFIRST_PASSSECOND_PASSTHIRD_PASSFOURTH_PASS
1234R1R3R4R5
5678R3R4R5 
9101R3R4R5 
1112R2R3R4R5

 

 

I need to summarize as below

 

SUMMARYCOUNT
R11
R21
R34
R44
R54

 

Note -

- R1, R2 are reason for pass.

- Pass Reason appears only once for each record

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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; 
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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
Onyx | Level 15

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; 
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



mohdfaisal89
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1312 views
  • 0 likes
  • 3 in conversation