How to count number of columns with values in an array?

Accepted Solution Solved
Reply
Contributor krm
Contributor
Posts: 26
Accepted Solution

How to count number of columns with values in an array?

[ Edited ]

Hi, 

 

I have a sample array below;

 

 

data have;
  input 
  owner_id $ 1-2 car1 $ 3-6 car2 $ 8-11 car3 $13-16 car4 $18-21 car5 $23-26;
datalines;
1 1231 2131 XXXX
2 0999 3232 9099 1111
3 5675 6776 5675 4564 3445
;

 

 

I want to count each column which has a value as "1" (besides "XXXX") and create a column called "with_mileage". If a column has XXXX in it I want to create another column called "no_mileage", please see the sample below:

 

 

 

data want;
  input 
  owner_id $ 1-2 car1 $ 3-6 car2 $ 8-11 car3 $13-16 car4 $18-21 car5 $23-26 with_mileage $ 27-28 no_mileage $ 30-31;
datalines;
1 1231 2131 XXXX           2 1
2 0999 3232 9099 1111      4 0
3 5675 6776 5675 4564 3445 5 0
;

 

Also another question is: in this sample the car number is fixed at 5. In the data that I have, I am transposing the data to get the array. However the number of columns may change with each update. Is it possible to write a code that automatically picks up the number of columns rather than assigning a fixed value? 

 

I am not really familiar with loops and arrays so any help would be greatly appreciated!

 

Thank you!


Accepted Solutions
Solution
‎08-08-2016 07:25 PM
Respected Advisor
Posts: 4,995

Re: How to count number of columns with values in an array?

Using the transposed data:

 

data want;

set have;

array cars {*} car: ;

with_mileage=0;

no_mileage=0;

do _n_=1 to dim(cars);

   if cars{_n_}='XXXX' then no_mileage + 1;

   else if cars{_n_} > ' ' then with_mileage + 1;

end;

run;

 

The abbreviation CAR: refers to all variable names that begin with CAR.  So there better not be any surprise names for variables that begin with CAR but aren't supposed to be counted in the array.

 

 

View solution in original post


All Replies
Grand Advisor
Posts: 17,406

Re: How to count number of columns with values in an array?

Do you have the ability to change your input data? Are you reading the data from a text file?

 

 

Contributor krm
Contributor
Posts: 26

Re: How to count number of columns with values in an array?

No, it's not from a text file. I am getting my data from a SAS table, I have the ability to change it. 

Grand Advisor
Posts: 17,406

Re: How to count number of columns with values in an array?

Also another question is: in this sample the car number is fixed at 5. In the data that I have, I am transposing the data to get the array.

I was going to suggest this. Why not work with the data in the long format? It's easier to run a proc freq and count the data that way.
Super User
Super User
Posts: 6,365

Re: How to count number of columns with values in an array?

Count BEFORE you transpose.

proc sql noprint ;
   create table middle as 
   select owner_id, car
       , max( car='XXXXX') as no_milage
       , sum( case car in ('XXXXX',' ') 0 else 1 end) as with_milage
   from have
   group by owner_id 
   ;
quit;

proc transpose data=middle out=want  prefix=car;
  by owner_id with_milage no_milage ;
  var car ;
run;
Respected Advisor
Posts: 3,124

Re: How to count number of columns with values in an array?

What is the purpose of transposing? is it just for the array? if your purpose is to count it per owner_id, then there is simpler approach, such as;

proc sql;
create table want as
select *, sum(car ne 'XXXX' and not missing(car)) as with_mileage, sum(car eq 'XXXX') as no_mileage
from have
group by owner_id;
quit;

'have' is pre-tranposed data.

Solution
‎08-08-2016 07:25 PM
Respected Advisor
Posts: 4,995

Re: How to count number of columns with values in an array?

Using the transposed data:

 

data want;

set have;

array cars {*} car: ;

with_mileage=0;

no_mileage=0;

do _n_=1 to dim(cars);

   if cars{_n_}='XXXX' then no_mileage + 1;

   else if cars{_n_} > ' ' then with_mileage + 1;

end;

run;

 

The abbreviation CAR: refers to all variable names that begin with CAR.  So there better not be any surprise names for variables that begin with CAR but aren't supposed to be counted in the array.

 

 

Contributor krm
Contributor
Posts: 26

Re: How to count number of columns with values in an array?

Thank you everbody for the replies. As you mentioned above, adjusting the data before transposing was also on my mind, but I just wanted to see if there is any way to get the solution by using arrays (as I am trying to get more efficient in arrays). 

 

All the codes are really helpful I appreciate your time posting the possible solutions!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 416 views
  • 2 likes
  • 5 in conversation