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,920

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

Posted in reply to knveraraju91
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,920

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

Posted in reply to knveraraju91

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
  • 137 views
  • 2 likes
  • 2 in conversation