BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

Hello everyone,

 

I would like to ask a question to you. It is related to Proc Sql statement and Case When. I want to count the number "1" values in the"Numeric" variable also by grouping "Numeric". However, in the following example, the query brings two rows but actually, I expect just one row. Can somebody help me by checking the following sample, please?

 

Data Have;
Length Character $ 8 Numeric 8;
Infile Datalines Missover;
Input Character	Numeric;
Datalines;
33 1
33 2
33 3
33 3
33 1
33 2
33 2
33 3
33 3
;
Run;

PROC SQL;
Create Table Want As
Select Distinct Character
,Case
When Numeric=1 Then Count(Numeric)
End As CountNumeric
From Have
Where Character='33'
Group By Numeric;
QUIT;

My desired output;

Desired.png

 

Thank you

4 REPLIES 4
RahulG
Barite | Level 11
--One way

PROC SQL;
Create Table Want As
Select Character
,count( numeric) as count_numeric
From Have
Where Character='33' and numeric =1
Group By character;
QUIT;


--Alternate way
PROC SQL;
Create Table Want2 As
Select Character
,sum( case when  numeric=1 then 1 end ) as count_numeric
From Have
Where Character='33' 
Group By character;
QUIT;



turcay
Lapis Lazuli | Level 10

Thanks a lot, can you also give brief information about the reason? Why it was bring mising values in my query?

RahulG
Barite | Level 11

There are couple of problem in the query

1. Use of aggregate function and use of distinct at the same time do not make any sense. Because aggregate function itself result one row per group.

 

2. The query need to group by  using Character column but it has used group by Numeric. The Group by should used on grouping variable which is Character in this case.

 

3. Count function used in case when then statement do not make any sense. 

 

 

Ksharp
Super User
use HAVING clause:

Data Have;
Length Character $ 8 Numeric 8;
Infile Datalines Missover;
Input Character	Numeric;
Datalines;
33 1
33 2
33 3
33 3
33 1
33 2
33 2
33 3
33 3
;
Run;

PROC SQL;
Select Character,Numeric,count(*) As CountNumeric
From Have
Group By Character,Numeric
having Character='33' and Numeric=1;
QUIT;


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
  • 5868 views
  • 4 likes
  • 3 in conversation