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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.