topic Re: Read different value in excel and return value in SAS Programming

Read different value in excel and return value

ihlayyel — Sun, 08 Oct 2017 18:18:55 GMT

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?

Re: Read different value in excel and return value

art297 — Sun, 08 Oct 2017 18:11:06 GMT

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

Re: Read different value in excel and return value

ihlayyel — Sun, 08 Oct 2017 18:26:17 GMT

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.

Re: Read different value in excel and return value

art297 — Sun, 08 Oct 2017 19:47:23 GMT

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

Re: Read different value in excel and return value

ihlayyel — Mon, 09 Oct 2017 06:18:39 GMT

##-

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.
-##

Re: Read different value in excel and return value

andreas_lds — Mon, 09 Oct 2017 07:03:06 GMT

Read the log, the error message has an obvious solution.

Re: Read different value in excel and return value

ihlayyel — Mon, 09 Oct 2017 07:29:27 GMT

Iam still can't figure it out.

Re: Read different value in excel and return value

Ksharp — Mon, 09 Oct 2017 13:18:43 GMT

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;