topic Re: Need counts of subjects available in visit in SAS Programming

Need counts of subjects available in visit

ambadi007 — Thu, 26 Dec 2024 11:17:15 GMT

i have my data like below

Usubjid Avisit
100 WK1
100 WK2
101 WK1

My requirement is to find how many times the subjects appeared in visits , so the needed data should be like below

visit count of subjects in visits
WK1 2
WK2 1

Please do the needful to find this

Re: Need counts of subjects available in visit

PaigeMiller — Thu, 26 Dec 2024 12:00:20 GMT

proc freq data=have;
    tables avisit;
run;

Re: Need counts of subjects available in visit

ambadi007 — Thu, 26 Dec 2024 14:14:35 GMT

I have used below codes and got the counts correctly as i required, but i need to show the corresponding visits also to the data. could you please modify my codes on

proc freq data=mn ;
tables usubjid/out=temp(rename=(count=count1));

run;

proc freq data=temp;
tables count1/out=temp1(keep=count) ;

run;

proc freq data=mn ; 
 tables usubjid/out=temp(rename=(count=count1)); 

 run; 
 
  proc freq data=temp; 
 tables count1/out=temp1(keep=count) ; 

 run;

Re: Need counts of subjects available in visit

PaigeMiller — Thu, 26 Dec 2024 15:33:37 GMT

I have used below codes and got the counts correctly as i required, but i need to show the corresponding visits also to the data.

I do not understand what you want. Please explain in more detail, or better yet, show us. Also, you seem to have changed the original question from counting by weeks to counting by USUBJID, please explain that as well.

Re: Need counts of subjects available in visit

ambadi007 — Thu, 26 Dec 2024 15:55:56 GMT

i have the example data

here we can see the visits number 8 having 2 subjects so the count of subject is 2 here , this is the brief of the data we have

Visits Sub count

5 3 2

6 0

7 1 1

8 5 2

Re: Need counts of subjects available in visit

Kurt_Bremser — Thu, 26 Dec 2024 17:03:04 GMT

And what should the result from this example data look like, and do you want it as a dataset or a report?

Re: Need counts of subjects available in visit

ambadi007 — Thu, 26 Dec 2024 17:08:40 GMT

I want it as a dataset

Re: Need counts of subjects available in visit

Kurt_Bremser — Thu, 26 Dec 2024 18:17:03 GMT

Please answer both parts of my question. Post the expected result from this example data:

Visits     Sub count



5            3            2

6                           0

7            1            1

8 5 2

8 5 2

Re: Need counts of subjects available in visit

PaigeMiller — Thu, 26 Dec 2024 18:48:08 GMT

@ambadi007

I said: "I do not understand what you want. Please explain in more detail, or better yet, show us. Also, you seem to have changed the original question from counting by weeks to counting by USUBJID, please explain that as well."

to which you replied

@ambadi007 wrote:

i have the example data

here we can see the visits number 8 having 2 subjects so the count of subject is 2 here , this is the brief of the data we have

Visits     Sub count



5            3            2

6                           0

7            1            1

8 5 2

8 5 2

I don't see the connection between this table above and the original one you posted, nor do I understand if this table is the input data or the output data or something else. Please provide clear complete descriptions, emphasis on COMPLETE, emphasis on CLEAR. Please provide both input data and desired output.

Re: Need counts of subjects available in visit

Patrick — Fri, 27 Dec 2024 00:13:59 GMT

If I have correctly understood your question, the following should provide the desired result.

data have;
  input Usubjid $ Avisit $;
  datalines;
100 WK1
101 WK1
100 WK2
;
run;

proc sql;
/*   create table want as */
  select 
  	Usubjid
  	,count(*) as Count label='count of subjects in visits'
  from have
  group by Usubjid;
quit;

Re: Need counts of subjects available in visit

ambadi007 — Fri, 27 Dec 2024 01:28:16 GMT

This count is correct , but I want to show the visit column also in the dataset.. that is my exact requirement

Re: Need counts of subjects available in visit

Ksharp — Fri, 27 Dec 2024 01:29:43 GMT

data have;
input Usubjid Avisit $;
cards;
100          WK1
100        WK2
101           WK1
;

proc sql;
create table want as
select avisit,count(distinct usubjid) as count
 from have
  group by avisit;
quit;

Re: Need counts of subjects available in visit

ambadi007 — Fri, 27 Dec 2024 02:24:10 GMT

The code you have provided will give number subjects in each visits.. i want how many visits have got each subjects . the below example code will give the desired result, but i am not able to add the corresponding visits in to it, if i add the results are going different , pls see my code

 proc freq data=mn ; 
 tables usubjid/out=temp(rename=(count=count1)); 
 run; 

  proc freq data=temp; 
 tables count1/out=temp1(keep=count) ; 
 run;

Re: Need counts of subjects available in visit

Ksharp — Fri, 27 Dec 2024 02:51:16 GMT

I am unable to understand your question.
Better post some MORE data and DESIRED output to clarify your problem.

Re: Need counts of subjects available in visit

ambadi007 — Fri, 27 Dec 2024 03:34:06 GMT

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

Re: Need counts of subjects available in visit

Tom — Fri, 27 Dec 2024 17:41:47 GMT

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;

Re: Need counts of subjects available in visit

Ksharp — Fri, 27 Dec 2024 06:15:58 GMT

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;

Re: Need counts of subjects available in visit

Kurt_Bremser — Fri, 27 Dec 2024 16:24:04 GMT

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