Hi,
I'm looking to count value of columns if it s not '00000' by each id. Below is my code and desired output. Basically, i dont want to count any column if the value is 00000. Any help is much appreciated!
Data a;
Input grp $3. Mon : $3. col1 : $5. col2 : $5. col3 : $5. col4 : $5.;
Infile cards;
Cards;
AGH JAN 45433 99348 23489 00000
NIK AUG 45667 81290 33667 77552
DET DEC 63644 56712 00000 00000
AAA MAR 78996 00000 00000 00000
UCG DEC 00000 00000 00000 00000
;
Run;
Output
ID MonTotal
AGH JAN 3
NIK AUG 4
DET DEC 2
AAA MAR 1
UCG DEC 0
Thanks!
Data a;
Input grp :$8. Mon : $8. col1 : $5. col2 : $5. col3 : $5. col4 : $5.;
Infile cards;
Cards;
AGH JAN 45433 99348 23489 00000
NIK AUG 45667 81290 33667 77552
DET DEC 63644 56712 00000 00000
AAA MAR 78996 00000 00000 00000
UCG DEC 00000 00000 00000 00000
;
Run;
/* data step*/
data want;
set a;
array col col:;
total=0;
do over col;
if col ne '00000' then total+1;
end;
drop col:;
run;
/*Proc SQL*/
proc sql;
create table want_sql as
select grp,mon,sum(col1 ne '00000',
col2 ne '00000',
col3 ne '00000',
col4 ne '00000') as total
from a;
quit;
Haikuo
Data a;
Input grp :$8. Mon : $8. col1 : $5. col2 : $5. col3 : $5. col4 : $5.;
Infile cards;
Cards;
AGH JAN 45433 99348 23489 00000
NIK AUG 45667 81290 33667 77552
DET DEC 63644 56712 00000 00000
AAA MAR 78996 00000 00000 00000
UCG DEC 00000 00000 00000 00000
;
Run;
/* data step*/
data want;
set a;
array col col:;
total=0;
do over col;
if col ne '00000' then total+1;
end;
drop col:;
run;
/*Proc SQL*/
proc sql;
create table want_sql as
select grp,mon,sum(col1 ne '00000',
col2 ne '00000',
col3 ne '00000',
col4 ne '00000') as total
from a;
quit;
Haikuo
Thank you!!
Hi,
Try this:
data b ;
set a ;
array col(*) col1 - col4 ;
total = 0 ;
do i = 1 to dim(col) ;
if col(i) ne "0000" then total = total + 1 ;
end;
keep Mon total ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.