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
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;
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).
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?
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.
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 !
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;
This is exactly what i was working on.. Thanks Mate !
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.
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.
Ready to level-up your skills? Choose your own adventure.