just would like to compare cell entries and return values.
coustmer_NO id A1 A2 A3 A4
1 5 10 20 45 0
1 13 0 45 2 5
2 4 0 10 7 8
2 3 7 9 55 0
2 10 0 0 0 0
3 4 90 8 14 3
3 10 20 7 4 15
how to count the ID that has (value > 030) for each customer_no
then, the min number of values before 030 appears.
The expected output would be something like:
cosutmer_no , count_ac_num , values
1 2 1
2 1 1
3 1 3
first I need compare each A value to your cutoff of 30. Then perhaps i want to index number of the first A value that is > 30? And then compare the min of that over every row?
You would have to explain where your values numbers come from. Why do customers 1 and 2 have values of 1, while customer 3 has a value of 3.
Art, CEO, AnalystFinder.com
If there is a maximum of one a1 to a4 value that can be greater than 30, then the following should work:
data have;
input customer_NO id A1-A4;
cards;
1 5 10 20 45 0
1 13 0 45 2 5
2 4 0 10 7 8
2 3 7 9 55 0
2 10 0 0 0 0
3 4 90 8 14 3
3 10 20 7 4 15
;
data want (keep=customer_NO count_ac_num values);
set have;
by customer_NO;
if first.customer_NO then do;
count_ac_num=0;
values=4;
end;
retain values;
array a_array(*) a1-a4;
do i=1 to dim(a_array);
if a_array(i) gt 30 then do;
count_ac_num+1;
values=min(values,dim(a_array)-i);
leave;
end;
else if i eq dim(a_array) then values=min(values,dim(a_array));
end;
if last.customer_NO then output;
run;
Otherwise, you'll have to specify how you'd want to treat VALUES in such cases.
Art, CEO, AnalystFinder.com
Assuming I understand what you are talking about.
data have;
input customer_NO id A1-A4;
cards;
1 5 10 20 45 0
1 13 0 45 2 5
2 4 0 10 7 8
2 3 7 9 55 0
2 10 0 0 0 0
3 4 90 8 14 3
3 10 20 7 4 15
;
proc sql;
select customer_NO,
sum(A1>30) as a1,
sum(A2>30) as a2,
sum(A3>30) as a3,
sum(A4>30) as a4,
calculated a1+calculated a2+calculated a3+calculated a4 as count_ac_num ,
case when
calculated a1=0 and calculated a2=0 and calculated a3=0 and calculated a4=0 then 4
when
calculated a2=0 and calculated a3=0 and calculated a4=0 then 3
when
calculated a3=0 and calculated a4=0 then 2
when
calculated a4=0 then 1
else 0 end as values
from have
group by customer_NO;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.