topic Re: How to output all OBS when Proc SQL statement is used with where ststement in SAS Programming

How to output all OBS when Proc SQL statement is used with where ststement

knveraraju91 — Mon, 23 Jan 2017 22:36:09 GMT

Dear,

I am trying to calculate number of subjects based on a condition.

With my code, I donot see all OBS in the output. How to get all OBS. Please help. Thank you

Code:

proc sql;

create table one as

select *, count(distinct id) as NS

from data1

where flag='Y';

quit;

Data1

id flag

1 Y

2 Y

5 Y

7 Y

output expected:

Data2

id flag NS

1 Y 4

2 Y 4

5 Y 4

7 Y 4

output getting

Data1

id flag NS

1 Y 4

2 Y 4

5 Y 4

7 Y 4

Re: How to output all OBS when Proc SQL statement is used with where ststement

PGStats — Mon, 23 Jan 2017 22:43:37 GMT

proc sql;
create table one as
select *, (select count(distinct id) from data1 where flag="Y") as NS
from data1;
quit;

Re: How to output all OBS when Proc SQL statement is used with where ststement

knveraraju91 — Tue, 24 Jan 2017 01:13:43 GMT

Thank you for the code. It worked.

How to include one more ststement as I have to calculate number of subjects by another variable(where AFLAG='Y') in same data step.

your code:

proc sql;
create table one as
select *, (select count(distinct id) from data1 where flag="Y") as NS
from data1;
quit;

Re: How to output all OBS when Proc SQL statement is used with where ststement

knveraraju91 — Tue, 24 Jan 2017 01:15:05 GMT

Thank you for the code. It worked.

How to include one more ststement as I have to calculate number of subjects by another variable(where AFLAG='Y') in same data step.

Your code

proc sql;
create table one as
select *, (select count(distinct id) from data1 where flag="Y") as NS
from data1;
quit;

Re: How to output all OBS when Proc SQL statement is used with where ststement

PGStats — Tue, 24 Jan 2017 04:43:07 GMT

Experiment and understand how this query works. Extending it should then become obvious.

proc sql;
create table two as
select 
	*, 
	(select count(distinct id) from data1 where flag="Y") as NS,
	(select count(distinct id) from data1 where aflag="Y") as aNS
from data1;
quit;