Ooops thank you. I thought it was Toms post. Thank you for your help as well!
Now that you have made some progress perhaps you can explain in more detail the overall picture.
Looking at your final program and the 15 sample records you posted it looks like you really only have TWO variables in your source table. The SYMPTOM and SYMPTOM_NO fields have the same information. When SYMPTOM_NO=1 then SYMPTOM is always equal to "Heartburns".
data have ;
infile cards truncover ;
input id_no symptom_no symptom $20. ;
cards;
1 1 Heartburns
1 2 Sickness
4 5 Tiredness
6 1 Heartburns
7 4 Temperature
8 1 Heartburns
8 5 Tiredness
9 2 Sickness
9 3 Spasm
9 4 Temperature
10 3 Spasm
10 5 Tiredness
11 3 Spasm
11 5 Tiredness
12 4 Temperature
12 5 Tiredness
13 3 Spasm
13 4 Temperature
14 3 Spasm
15 1 Heartburns
;
So what is it that you want to calculate in the data step? Do you want to count how many times a given ID got a particular symptom? Perhaps you want to use the symptom names as the variable names in your array? Also what is this DISEASE variable you mentioned? Is that something you are supposed to calculate? Is it the count of how many records each ID had?
Perhaps you want something like this?
This is an example of what is called a DOW loop. It eliminates the need for retaining variables or an OUTPUT statement since it processes all of the records for a given BY group in the same iteration of the data step.
data want ;
do until (last.id_no);
set have ;
by id_no;
array counts Heartburns Sickness Spasm Temperature Tiredness ;
if first.id_no then do _n_=1 to dim(counts);
counts(_n_)=0;
end;
counts(symptom_no)=counts(symptom_no)+1;
disease=sum(disease,1);
end;
keep id_no Heartburns Sickness Spasm Temperature Tiredness Disease ;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.