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

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

krm — Mon, 08 Aug 2016 20:35:48 GMT

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!

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

Reeza — Mon, 08 Aug 2016 20:46:40 GMT

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

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

krm — Mon, 08 Aug 2016 20:51:36 GMT

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

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

Reeza — Mon, 08 Aug 2016 20:56:32 GMT

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.

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

Tom — Mon, 08 Aug 2016 20:58:02 GMT

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;

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

Haikuo — Mon, 08 Aug 2016 21:03:42 GMT

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.

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

Astounding — Mon, 08 Aug 2016 21:04:39 GMT

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.

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

krm — Mon, 08 Aug 2016 23:28:56 GMT

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!