That helps clarify what you want. It also helps explain why you were having a hard time explaining, because that is not a normal way to store (or really even present) data. You will need to create a text string to store the LIST of subject ids.
If the data is sorted by VISIT then you can make that data with a data step. Make sure to make the character variable long enough to store the longest possible list of subjects.
data want;
do num_subjects=1 by 1 until(last.visit);
set have;
by visit;
length list_of_subjects $200;
list_of_subjects=catx(',',list_of_subjects,subject);
end;
keep visit num_subjects list_of_subjects;
run;
If you need the observation for WEEK3 to appear then you need a source dataset that has all of the possible VISIT values. You can then merge the two by VISIT to make sure the empty visit appears.
data want;
merge all_visits want;
by visit;
num_subjects+0;
run;
Results:
You can do the merge in the same step that does the counting. You just need to modify the way you count a little so that zero is a possible result.
data want;
do until(last.visit);
merge all_visits have(in=in1);
by visit;
num_subjects=sum(num_subjects,in1);
length list_of_subjects $200;
list_of_subjects=catx(',',list_of_subjects,subject);
end;
keep visit num_subjects list_of_subjects;
run;
Same as Tom's code:
data have; input Usubjid Avisit $; cards; 100 WK1 100 WK2 101 WK1 ; proc sort data=have out=temp nodupkey; by Avisit Usubjid; run; data want; do num_subjects=1 by 1 until(last.avisit); set temp; by avisit; length list_of_subjects $ 2000; list_of_subjects=catx(',',list_of_subjects,usubjid); end; keep avisit num_subjects list_of_subjects; run;
@ambadi007 wrote:
See the below requirement , i want the 3rd column , please see the Week5 here 2 subjects achieved response , so the count of subjects will be 2 .. like wise i need to create a dataset
Visits where response achieved Sub No who achieved response Count of Subject Number week1 4 1 Week2 3 1 week3 0 week4 1 1 week5 2, 5 2
And from which input data should this result be calculated?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.