## Hi, I have just strated learning SAS and need help.

Solved
Occasional Contributor
Posts: 19

# Hi, I have just strated learning SAS and need help.

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.

Accepted Solutions
Solution
‎08-06-2014 10:53 AM
Posts: 1,231

## Re: Hi, I have just strated learning SAS and need help.

proc sql;

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

quit;

All Replies
Super User
Posts: 7,997

## Re: Hi, I have just strated learning SAS and need help.

Hi,

data want;

set have;

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

run;

Super User
Posts: 19,878

## Re: Hi, I have just strated learning SAS and need help.

Are your columns numeric, character or some of each?

Occasional Contributor
Posts: 19

numeric

Super User
Posts: 19,878

## Re: Hi, I have just strated learning SAS and need help.

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);

Occasional Contributor
Posts: 19

## Re: Hi, I have just strated learning SAS and need help.

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.

Super User
Posts: 19,878

## Re: Hi, I have just strated learning SAS and need help.

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.

Solution
‎08-06-2014 10:53 AM
Posts: 1,231

## Re: Hi, I have just strated learning SAS and need help.

proc sql;

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

quit;

Occasional Contributor
Posts: 19

## Re: Hi, I have just strated learning SAS and need help.

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.

Occasional Contributor
Posts: 19

## Re: Hi, I have just strated learning SAS and need help.

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.

🔒 This topic is solved and locked.