Want the counts aggregately by visit wise.

[ambadi007]

I have a data like below.

Subject Visit Treatment
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB

i NEED THE SUBJECT COUNTS BASED ON TRAETMENT BY AGRAGATE BY VISIT WISE , SO THE DATA SHOULD LOOKS LIKE BELOW

VISIT TRTA TRTB OVERALL
Week1    2        2                 4
Week2    4         3                7
Week3    8          5             13

THE SUBJECTS COUNTS SHOULD BE SHOW AGRAGRATE BY VISIT AS SHOWN ABOVE

[PaigeMiller]

Please do not type in ALL CAPITALS.

proc freq data=have;
    tables visit*treatment/nocol norow nopercent;
run;

[ambadi007]

hi @PaigeMiller thanks for the reply, but as i mentioned i need to present like aggregate by each visit 

for example if one subject is participated in TRTA in Week1 and Week 2 then the count should be 2 in week2..like the below the counts are aggregating visit wise 

Vsit        TRTA   TRTB        Overall 

Week1    2        2                 4
Week2    4         3                7
Week3    8          5             13

[PaigeMiller]

---

@ambadi007 wrote:

as i mentioned i need to present like aggregate by each visit 

for example if one subject is participated in TRTA in Week1 and Week 2 then the count should be 2 in week2

---

data aggregate;
    set have;
    by subject;
    if first.subject then aggregate=0;
    aggregate+1;
run;
proc freq data=aggregate;
    tables visit*aggregate/nocol nopercent;
run;

[ambadi007]

this is the counts I am getting after trying... i think the way i am saying is little confucing.. I have aded a new column "Needed."
Its needed if 1 subject is participated week1 and it is also participated in week 2 then week2 should show 2 subjects from week1 and 2 subjects from week2
and should show 4 as count ... hope now it is clear

vsit       aggregate      trt        COUNT          Needed
Week1   1             TRTA           2                   2 (Partcipated in week1)
Week2  1             TRTA           2                    4 (Week2 +Week1 count)
continue .......

[ballardw]

---

@ambadi007 wrote:

I have a data like below.

Subject Visit Treatment
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB

i NEED THE SUBJECT COUNTS BASED ON TRAETMENT BY AGRAGATE BY VISIT WISE , SO THE DATA SHOULD LOOKS LIKE BELOW

VISIT TRTA TRTB OVERALL
Week1    2        2                 4
Week2    4         3                7
Week3    8          5             13

THE SUBJECTS COUNTS SHOULD BE SHOW AGRAGRATE BY VISIT AS SHOWN ABOVE

---

Please make sure that your "want" result can be made from the given example data.

I see no way that you can arrive at 13 for an overall count given there are only 9 observations in the example.

If you mean: accumulate by Week and then aggregate across the week in a running total then show exactly which observations are counted for which output observation.

I believe we may also have suggested providing data in the form of data step code so we don't have to make guesses about your actual variable types or names.

Here is my guess given the incomplete example data provided but it does match the desired output for the first two rows.

Note that I add two variables to hold the running totals so you can actually examine if they are built in correctly. If so the you can rename them on the output data set.

data have;
  input Subject $ Visit $ Treatment $;
datalines;
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB
;

proc freq data=have noprint;
   tables visit * treatment/out=firstcount(drop=percent);
run;

Proc transpose data=firstcount 
     out=trans (drop=_name_ _label)
;
   by visit;
   id treatment;
   var count;
run;

data runningtotal;
   set trans;
   retain r1 r2;
   r1= sum(r1,trta);
   r2= sum(r2,trtb);
   overall= sum(r1,r2);
run;

[AhmedAl_Attar]

@ambadi007 

Here is another potential solution

data have;
  input Subject $ Visit $ Treatment $;
datalines;
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB
;
run;

/* Sort the data to support the want layout */
Proc sort data=have;
	by visit Treatment;
Run;

/* Store the unique treatment values in a space delimited macro variable */
proc sql noprint;
	select distinct Treatment
	into :treats separated by ' '
	from have;
quit;

/* Store the count of unique treatment values */
%let t_cnt = &sqlobs;

/* Generate accumulative totals */
data want(KEEP=visit &treats OVERALL);
	if 0 then set have;
	ARRAY trts {&t_cnt} 3 &treats;
	Retain &treats;
	
	do until (eof);
		SET have end=eof;
		By visit treatment;

		/* Increment the count of treatment variable as needed */
		do i=1 to dim(trts);
			trts[i]+ifc(treatment=vname(trts[i]),1,0) ;
		end;
		
		if (last.visit) then
		do;
			OVERALL = sum(of trts(*));
			output;
		end;
	end;
run;

Hope this helps

[ambadi007]

Hi Thanks for the reply, I was getting the below results while runnig the code,

Visit              TRTA TRTB OVERALL
Week1              2       2           4
Week2              4       3           7
Week3             6(Week1+Week2+Week3.so this should be 8 ) 

Basically I need the results like count of subjects present in Week1+Week2+Week3 and so on .
so I have edited the needed results in TRTA column 2 from week1 and 4 from week2, and the week3 count is 2 so the count for
week3 should be 8 . could you please help me

[AhmedAl_Attar]

@ambadi007
Week3 - TRTA = 6 is the correct accumulative count. While 8 would mean you have double counted TRTA somewhere!
The sample data set only has 6 entries for TRTA.

[Ksharp]

data have;
  input Subject $ Visit $ Treatment $;
datalines;
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB
;

proc freq data=have noprint;
table Visit*Treatment /out=temp list nopercent nocol norow;
run;
proc transpose data=temp out=temp2(drop=_name_ _label_);
by visit;
var count;
id treatment;
run;
data want;
 set temp2;
 array a{99} _temporary_;
 array b{99} _temporary_;
offset_a=0;
offset_b=0;
do i=1 to _n_-1;
 offset_a+a{i};
 offset_b+b{i};
end;
trta=sum(trta,offset_a);
trtb=sum(trtb,offset_b);
a{i}=trta;
b{i}=trtb;
overall=sum(trta,trtb);
drop offset_: i;
run;

[AhmedAl_Attar · Accepted solution]

Hi @ambadi007 

Thanks to @Ksharp for the inspiration , here is a solution that supports more than the two treatments (TRTA, TRTB) in the Sample (have) data. 

data have;
  input Subject $ Visit $ Treatment $;
datalines;
100 Week1    TRTA
100 Week2   TRTA
100 Week3   TRTA
101 Week1   TRTB
102 Week1   TRTA
102 Week2  TRTA
102 Week3  TRTA
103 Week1 TRTB
103 Week2 TRTB
;
run;

/* Aggregate Weekly visits counts by treatment */
proc summary data=have nway;
	class visit treatment;
	output out=temp(drop=_type_ rename=(_freq_=cnt));
run;

/* Convert treatment Row to treatment Columns */
proc transpose data=temp out=temp2(drop=_:);
	by visit;
	id treatment;
	var	cnt;
run;

/* Create macro variables that will be used to derive dynamic code */
proc sql noprint;
	select distinct strip(treatment)
		, catx('=',treatment,cats('_',treatment))
		, cats('_',treatment)
	into  :trts separated by ' '
		, :trts_rename separated by ' '
		, :_trts separated by ' '
	from temp;
quit;
%let trts_cnt=&sqlobs;

/* Create data set to be used by Hash Object */
data work.temp;
	SET temp2(drop=visit rename=(&trts_rename));
	rid+1;
run;

DATA want(KEEP=visit &trts overall);

/* Define the variable in the PDV */
	if (0) then set temp2 temp;

	/* Declare arrays to group variables */
	array trts_arr {&trts_cnt} &trts;   * actual counts; 
	array _trts_arr {&trts_cnt} &_trts; * accumulated counts;

	/* Populate the Hash Object and Hash Iterator */
	if (_n_=1) then
	do;
		dcl hash h(dataset:'work.temp',ordered:'a');
		h.definekey('rid');
		h.definedata(all:'yes'); 
		h.definedone();
		dcl hiter hi('h');
	end;

	SET temp2;

	/* Reset to the first item on the Hash */
	rc=hi.first();

	/* Calculate accumulated counts */
	do i=1 to (_n_ - 1) while(rc=0);
		do j=1 to dim(trts_arr);
			trts_arr[j] = sum(trts_arr[j],_trts_arr[j]);
		end;
		rc=hi.next();
	end;
	overall = sum(of trts_arr(*));

	/* Update the stored info in the Hash Object */
	do j=1 to dim(trts_arr);
		_trts_arr[j] = trts_arr[j]; 
	end;
	h.replace();
RUN;

Hope this helps

[ambadi007]

Thanks for the help .. this works fine