BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas_kms
Calcite | Level 5

I trying to right a query to calculate count.  Lets say I have 4 columns M1, M2, M3 and Count.with 100 rows.  I want a query to calculate number values available in each row and give me count of values in Count column, excluding missing values.

Example: If row1 has values in M1, M2 and M3,  Count will be 3. If in row 2 value of M2 is missing, count will be 2.  Query should not be limited to number of rows, since data can sometimes have 100 rows or 1000.

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

proc sql;

select *,3-cmiss(m1,m2,m3) as count from have;

quit;

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

data want;

     set have;

     count=4 - nmiss(of m1-m4);

run;

Reeza
Super User

Are your columns numeric, character or some of each?

Reeza
Super User

Use the N function.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

count=n(m1, m2, m3...etc);

OR

count=n(of m1-m4);

sas_kms
Calcite | Level 5

Thanks...Reeza.

What if it is based on three month value check:

A variable which tracked over three months, we neglect the missing but observe how many times the value of that variable changes, meaning if M1value was 1 and it changed to 2 in m2 and then in M3 which is the observed month it changed to 3 so total number of distinct changes are 3.  So I would like query to show how many distinct changes happened to this variable.  If the value stayed 2 through out then it is not a change.

Reeza
Super User

This is a new question and should probably be posted as such. Also, include a sample of what you want the output to look like.

stat_sas
Ammonite | Level 13

proc sql;

select *,3-cmiss(m1,m2,m3) as count from have;

quit;

sas_kms
Calcite | Level 5

What if it is based on three month value check:

A variable which tracked over three months, we neglect the missing but observe how many times the value of that variable changes, meaning if M1value was 1 and it changed to 2 in m2 and then in M3 which is the observed month it changed to 3 so total number of distinct changes are 3.  So I would like query to show how many distinct changes happened to this variable.  If the value stayed 2 through out then it is not a change.

sas_kms
Calcite | Level 5

Please assist:

What if it is based on three month value check:

A variable which tracked over three months, we neglect the missing but observe how many times the value of that variable changes, meaning if M1value was 1 and it changed to 2 in m2 and then in M3 which is the observed month it changed to 3 so total number of distinct changes are 3.  So I would like query to show how many distinct changes happened to this variable.  If the value stayed 2 through out then it is not a change.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1275 views
  • 6 likes
  • 4 in conversation