Hello,
I have a very large data set which looks similar to this:
ID Diag1 Diag2 Diag3 Diag4 Diag5 etc...
1 T401 B403 C005 D054 F403
2 B504 F05 L094 M504 N093
3 LT409 G056 M095 X09 Y06
I would like to use an array to create a counter variable to count the number of times a record has a "T" or "F" diagnosis, like this:
ID Diag1 Diag2 Diag3 Diag4 Diag5 etc... Counter
1 T401 B403 C005 D054 F403 2
2 B504 F05 L094 M504 N093 1
3 LT409 G056 M095 X09 Y06 0
I know how to code an array, but I'm not sure how to incorporate the counter. Here's what I have for my array code:
data base;
set base;
array DIAG{25} $7 DIAG1 - DIAG25;
array DIAGTY{25} $1 DIAGTY1 - DIAGTY25;
do i=01 to 25;
if substr(DIAG(i),1,3) in ('T36') or
substr(DIAG(i),1,3) in ('T37') or
substr(DIAG(i),1,3) in ('T38') or
substr(DIAG(i),1,3) in ('T39') or
substr(DIAG(i),1,4) in ('T400') or
substr(DIAG(i),1,4) in ('T401') or
substr(DIAG(i),1,4) in ('T402') or
substr(DIAG(i),1,4) in ('T403') or
substr(DIAG(i),1,4) in ('T404') or
substr(DIAG(i),1,4) in ('T405') or
substr(DIAG(i),1,4) in ('T406') or
substr(DIAG(i),1,4) in ('T408') or
substr(DIAG(i),1,4) in ('T409') or
substr(DIAG(i),1,3) in ('T41') or
substr(DIAG(i),1,3) in ('T42') or
substr(DIAG(i),1,3) in ('T43') or
substr(DIAG(i),1,3) in ('T44') or
substr(DIAG(i),1,3) in ('T45') or
substr(DIAG(i),1,3) in ('T46') or
substr(DIAG(i),1,3) in ('T47') or
substr(DIAG(i),1,3) in ('T48') or
substr(DIAG(i),1,3) in ('T49') or
substr(DIAG(i),1,3) in ('T50') or
substr(DIAG(i),1,3) in ('T51') or
substr(DIAG(i),1,4) in ('F100') or
substr(DIAG(i),1,4) in ('F110') or
substr(DIAG(i),1,4) in ('F130') or
substr(DIAG(i),1,4) in ('F140') or
substr(DIAG(i),1,4) in ('F150') or
substr(DIAG(i),1,4) in ('F160') or
substr(DIAG(i),1,4) in ('F170') or
substr(DIAG(i),1,4) in ('F180') or
substr(DIAG(i),1,4) in ('F190')
then do; count=1; end; /*What can I put here to get it to count the number of times these codes appear in each record?*/
end;
run;
Thanks for your help!
data base2;
set base;
array DIAG{25} $7 DIAG1 - DIAG25;
array DIAGTY{25} $1 DIAGTY1 - DIAGTY25;
counter=0;
do i=01 to 25;
if substr(DIAG(i),1,3) in ('T36') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T37') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T38') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T39') then counter=counter+1;
/* etc. */
Does this LT409 not qualify?
No, it does not qualify. Only codes with the Prefix of T or F. Thanks for your help!
data have;
input ID (Diag1 Diag2 Diag3 Diag4 Diag5) ($) ;
cards;
1 T401 B403 C005 D054 F403
2 B504 F05 L094 M504 N093
3 LT409 G056 M095 X09 Y06
;
data want;
set have;
array t diag1-diag5;
Counter=0;
do over t;
if first(t) in ('T','F') then Counter=sum(Counter,1);
end;
run;
data base2;
set base;
array DIAG{25} $7 DIAG1 - DIAG25;
array DIAGTY{25} $1 DIAGTY1 - DIAGTY25;
counter=0;
do i=01 to 25;
if substr(DIAG(i),1,3) in ('T36') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T37') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T38') then counter=counter+1;
if substr(DIAG(i),1,3) in ('T39') then counter=counter+1;
/* etc. */
That worked perfectly, thanks a million 🙂
Use a loop with your array and SUBSTR() to get the first character of each code and check if that's a T or F.
data want;
set have;
array diag(25) $7 diag1-diag25;
counter = 0;
do i=1 to dim(diag);
if upcase(substr(diag(1), 1, 1)) in ('T', 'F') then counter+1;
end;
run;
@sas_newbie14 wrote:
Hello,
I have a very large data set which looks similar to this:
ID Diag1 Diag2 Diag3 Diag4 Diag5 etc...
1 T401 B403 C005 D054 F403
2 B504 F05 L094 M504 N093
3 LT409 G056 M095 X09 Y06
I would like to use an array to create a counter variable to count the number of times a record has a "T" or "F" diagnosis, like this:
ID Diag1 Diag2 Diag3 Diag4 Diag5 etc... Counter
1 T401 B403 C005 D054 F403 2
2 B504 F05 L094 M504 N093 1
3 LT409 G056 M095 X09 Y06 0
I know how to code an array, but I'm not sure how to incorporate the counter. Here's what I have for my array code:
data base;
set base;
array DIAG{25} $7 DIAG1 - DIAG25;
array DIAGTY{25} $1 DIAGTY1 - DIAGTY25;
do i=01 to 25;
if substr(DIAG(i),1,3) in ('T36') or
substr(DIAG(i),1,3) in ('T37') or
substr(DIAG(i),1,3) in ('T38') or
substr(DIAG(i),1,3) in ('T39') or
substr(DIAG(i),1,4) in ('T400') or
substr(DIAG(i),1,4) in ('T401') or
substr(DIAG(i),1,4) in ('T402') or
substr(DIAG(i),1,4) in ('T403') or
substr(DIAG(i),1,4) in ('T404') or
substr(DIAG(i),1,4) in ('T405') or
substr(DIAG(i),1,4) in ('T406') or
substr(DIAG(i),1,4) in ('T408') or
substr(DIAG(i),1,4) in ('T409') or
substr(DIAG(i),1,3) in ('T41') or
substr(DIAG(i),1,3) in ('T42') or
substr(DIAG(i),1,3) in ('T43') or
substr(DIAG(i),1,3) in ('T44') or
substr(DIAG(i),1,3) in ('T45') or
substr(DIAG(i),1,3) in ('T46') or
substr(DIAG(i),1,3) in ('T47') or
substr(DIAG(i),1,3) in ('T48') or
substr(DIAG(i),1,3) in ('T49') or
substr(DIAG(i),1,3) in ('T50') or
substr(DIAG(i),1,3) in ('T51') orsubstr(DIAG(i),1,4) in ('F100') or
substr(DIAG(i),1,4) in ('F110') or
substr(DIAG(i),1,4) in ('F130') or
substr(DIAG(i),1,4) in ('F140') or
substr(DIAG(i),1,4) in ('F150') or
substr(DIAG(i),1,4) in ('F160') or
substr(DIAG(i),1,4) in ('F170') or
substr(DIAG(i),1,4) in ('F180') or
substr(DIAG(i),1,4) in ('F190')then do; count=1; end; /*What can I put here to get it to count the number of times these codes appear in each record?*/
end;
run;
Thanks for your help!
Stand back! This is a job for SUPER PROCTRANSPOSE!
Make your life easier, convert your diagnosis values into a column, and then you can apply any tests you wish.
Tom
proc sort data=Have;
by ID;
run;
proc transpose data=Have out=HaveTransposed;
var Diag1-Diag5;
by ID;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.