DATA Step, Macro, Functions and more

How to count subjects by two where ststements in one Proc sql statement

Reply
Super Contributor
Posts: 272

How to count subjects by two where ststements in one Proc sql statement

Dear,

 

I am trying to calculate number of subjects based on different conditions.

 

Some one  helped me with code with one condition. I need to count number of subjects by several variables eg: where (aflag='Y'). How to code to get the output which includes all OBS in one proc statement . Thank you

 

Code:

 

 

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

Data1

id            flag              AFlag

1               Y                

1               Y

1               Y

2               Y                   Y

2               Y

3               

4                 

5                Y

6                                     Y

7                Y                   Y

 

output expected:

Data2

id            flag             NS                NS2

1               Y                4

1               Y                4

1               Y                 4

2               Y                 4                  3

2               Y                4                    3

3               

4                 

5                Y               4

6                                                        3

7                Y               4                      3

 

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

Trusted Advisor
Posts: 1,137

Re: How to count subjects by two where ststements in one Proc sql statement

Posted in reply to knveraraju91
try the proc sql

proc sql;
create table test as select a.*, b.ns,c.ns2 from have as a left join (select distinct count(distinct id) as ns,flag from have where flag ne '' group by flag) as b on a.flag=b.flag left join
(select distinct count(distinct id) as ns2,aflag from have where aflag ne '' group by aflag) as c on a.aflag=c.aflag order by a.id;
quit;
Thanks,
Jag
Ask a Question
Discussion stats
  • 1 reply
  • 106 views
  • 0 likes
  • 2 in conversation