BookmarkSubscribeRSS Feed
ihlayyel
Fluorite | Level 6

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?

7 REPLIES 7
art297
Opal | Level 21

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

 

ihlayyel
Fluorite | Level 6
Thank you so much for you comment:

for costumer 1, in the two raw there is a value bigger than >30. Then select the min number of cells which is in the first row, that have A4 only. that is why its 1 .

for customer 2 : there is only one value >30 . and only one value which is A4.

for customer 3 : there is one value > 30. and there is three values before it which are A2, A3 and A4.
art297
Opal | Level 21

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

ihlayyel
Fluorite | Level 6
##-

69 data want4 (keep=customer_NO count_ac_num values);
70 set WORK.test;
71 by customer_NO;
72 if first.customer_NO then do;
73 count_ac_num=0;
74 values=4;
75 end;
76 retain values;
77 array a_array(*) a1-a4;
78 do i=1 to dim(a_array);
79 if a_array(i) gt 30 then do;
80 count_ac_num+1;
81 values=min(values,dim(a_array)-i);
82 leave;
83 end;
84 else if i eq dim(a_array) then values=min(values,dim(a_array));
85 end;
86 if last.customer_NO then output;
87 run;
ERROR: BY variables are not properly sorted on data set WORK.TEST.
customer_no=3 acct_type=10 a1=0 a2=30 a3=0 a4=0 FIRST.customer_no=0 LAST.customer_no=0 count_ac_num=0 values=4 i=. _ERROR_=1 _N_=8
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 9 observations read from the data set WORK.TEST.
-##
andreas_lds
Jade | Level 19
Read the log, the error message has an obvious solution.
ihlayyel
Fluorite | Level 6
Iam still can't figure it out.
Ksharp
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 695 views
  • 0 likes
  • 4 in conversation