topic calculate for each customer Number of times that he touch grade 11 or grade 12 in SAS Programming

calculate for each customer Number of times that he touch grade 11 or grade 12

Ronein — Tue, 05 Mar 2019 11:36:13 GMT

Hello
I have multiple observations for each customer .(6 rows for each customer in months :1807,1808,1809,1810,1811,1812)
I want to calculate for each customer Number of times that he touch grade 11 or grade 12.
For ID 1 I expect to get value 3
For ID 2 I expect to get value 0
For ID 3 I expect to get value 1
May anyone show please How to do it in one step?
In the following code I show how to do it in two steps.
My challenge is to learn to do it in one step with lag or another way,

Data rawdata;
Input ID  month score;
cards;
1 1807 11
1 1808 12
1 1809 9
1 1810 8
1 1811 11
1 1812 9
2 1807 7
2 1808 6
2 1809 7
2 1810 7
2 1811 8
2 1812 8
3 1807 .
3 1808 9 
3 1809 9
3 1810 9
3 1811 10
3 1812 11
;
run;

/*Way1*/
Data tbl2;
set rawdata;
IF score in(11,12) then Touch_11_12_flag=1;
else Touch_11_12_flag=0;
Run;
PROC SQL;
	create table want1  as
	select ID, 
             sum(Touch_11_12_flag) as Total_Touch_11_12
	from  tbl2
	group by ID
;
QUIT;
proc print data=want1 noobs;
run;

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Kurt_Bremser — Tue, 05 Mar 2019 11:42:19 GMT

It's the same approach with a retained variable that worked for you in https://communities.sas.com/t5/SAS-Programming/Calculate-number-of-changes-in-score-during-6-months/m-p/540352

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

novinosrin — Tue, 05 Mar 2019 14:11:46 GMT

Data rawdata;
Input ID  month score;
cards;
1 1807 11
1 1808 12
1 1809 9
1 1810 8
1 1811 11
1 1812 9
2 1807 7
2 1808 6
2 1809 7
2 1810 7
2 1811 8
2 1812 8
3 1807 .
3 1808 9 
3 1809 9
3 1810 9
3 1811 10
3 1812 11
;
run;

PROC SQL;
	create table want1  as
	select ID, 
             sum(score in(11,12)) as Total_Touch_11_12
	from  rawdata
	group by ID
;
QUIT;

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

mkeintz — Tue, 05 Mar 2019 23:59:36 GMT

I see that @Kurt_Bremser noticed the same problem from you already in sas communities. Is there a reason to post it again? The usual result of this approach is to see subsequent new questions ignored - like the boy who cried wolf.

Help us help you - don't overload the people with useful answers.

regards,

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Ronein — Wed, 06 Mar 2019 06:15:09 GMT

Thank you so much!

data want3;
set rawdata;
by id;
oldscore = lag(score);
if first.id and score in(11,12) then Total_Touch_11_12 = 1;
else if first.id and score not in(11,12) then Total_Touch_11_12 = 0;
else if score in(11,12) then Total_Touch_11_12 + 1;
if last.id then output;
keep id Total_Touch_11_12;
run;
proc print data=want3 noobs;
run;

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Kurt_Bremser — Wed, 06 Mar 2019 07:07:07 GMT

You're overcomplicating. Such a step has three discrete elements:

initialize counter at first.id
increment counter on condition
output at last.id

So the step can easily be written as

data want3;
set rawdata;
by id;
if first.id then total_touch_11_12 = 0;
if score in (11,12) then total_touch_11_12 + 1;
if last.id then output;
keep id total_touch_11_12;
run;

Note that the increment statement

total_touch_11_12 + 1;

implies an automatic retain. If you need to do another type of calculation, you will need an explicit retain statement.

Since oldscore is not needed here (there's no comparison with a previous observation (to detect a change) in your logic), the assignment with the lag() function can also be omitted.

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Peter_C — Wed, 06 Mar 2019 08:53:04 GMT

Kurt
Why not introduce the OP to what has neen referred to as the DoW loop, eliminating the need for initialising and first. Code
The following assumes only that data is in ID order and any score above 10 is enough.
Data want ;
Do until( last.ID )
set rawdata ;
BY id ;
if score > 10 then touch11 = sum( 1, touch11 ) ;
end ;
If touch11 ;
Run;

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Kurt_Bremser — Wed, 06 Mar 2019 09:08:12 GMT

I'd write that as

data want;
do until (last.id);
  set rawdata;
  by id;
  if score in (11,12) then touch11 = sum(touch11,1);
end;
touch11 = max(0,touch11); * to prevent missing values;
keep id touch11;
run;

Both the "classical" data step method (first,increment,last) and the DOW loop require sorting and need only a single sequential pass through the sorted dataset, and are therefore functionally equivalent with (probably) only marginal differences in performance.

Re: calculate for each customer Number of times that he touch grade 11 or grade 12

Peter_C — Wed, 06 Mar 2019 09:57:08 GMT

Like brevity

Data want ;
Touch11=0;
Do until( last.ID )
set rawdata ;
BY id ;
if score > 10 then touch11 +1 ;
end ;
Run;

Interestingly the sample data appear to be in month order within ID. However, I cannot see a way to take advantage of the month-order.