SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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
Trusted Advisor
Posts: 1,204

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

proc sql;

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

quit;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

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: 17,730

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

Are your columns numeric, character or some of each?

Occasional Contributor
Posts: 19

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

numeric

Super User
Posts: 17,730

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: 17,730

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
Trusted Advisor
Posts: 1,204

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.

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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