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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.