DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 272

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

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

1               Y

1               Y

2               Y

2               Y

3               

4                 

5                Y

6                

7                Y

 

output expected:

Data2

id            flag             NS

1               Y                4

1               Y                4

1               Y                 4

2               Y                 4

2               Y                4

3               

4                 

5                Y               4

6                

7                Y               4

 

output getting

Data1

id            flag             NS

1               Y                 4

1               Y                 4

1               Y                 4

2               Y                 4

2               Y                  4          

5                Y                 4           

7                Y                 4

 

 

 

Respected Advisor
Posts: 4,659

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

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

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

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;

 

Super Contributor
Posts: 272

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

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;
Respected Advisor
Posts: 4,659

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

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;
PG
Ask a Question
Discussion stats
  • 4 replies
  • 131 views
  • 2 likes
  • 2 in conversation