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?
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.