BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

 

 

4 REPLIES 4
PGStats
Opal | Level 21
proc sql;
create table one as
select *, (select count(distinct id) from data1 where flag="Y") as NS
from data1;
quit;
PG
knveraraju91
Barite | Level 11

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;

 

knveraraju91
Barite | Level 11

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;
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 775 views
  • 2 likes
  • 2 in conversation