BookmarkSubscribeRSS Feed
Tom
Super User Tom
Super User

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:

Tom_0-1735273218923.png

 

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;

 

 

Ksharp
Super User

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;
Kurt_Bremser
Super User

@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?

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 17 replies
  • 2880 views
  • 0 likes
  • 6 in conversation