- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm trying to create a
Proc Sql ;
create table NEW as select *, sum (VARIABLE_1) as VAR1_SUM, count (distinct VARIABLE_2 where VARIABLE3=1) as VAR2_CNT
from HAVE group by week, store;
quit;
This is my current code and would not run.. Any suggestion?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It really help to start with posting your input data.
data have;
input var1-var3;
cards;
15 100 1
19 200 1
20 300 1
40 400 0
15 500 0
16 100 1
0 200 1
0 300 0
19 400 0
;
And your expected output data.
data want;
input sum_var1 cnt_var2;
cards;
144 3
;
That way others can provide tested code:
proc sql;
create table want as
select
sum(var1) as sum_var1
, count(distinct case when(var3=1) then var2 end) as cnt_var2
from have
;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You probably want to use a CASE clause.
count(case when (VARIABLE3=1) then VARIABLE_2 end ) as VAR2_CNT
Which will count how many observations have VARIABLE3=1 and VARIABLE_2 not missing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much! I would like to count the distinct number of VARIABLE_2 (not the total observation) where VARIABLE_3=1.
So the Proc Sql will give me the value 3 for VAR2_CNT (the VARIABLE_2 where variable3=1, 100,200,300) not 5 (total observation where variable3=1)
VARIABLE1 | VARIABLE2 | VARIABLE3 |
15 | 100 | 1 |
19 | 200 | 1 |
20 | 300 | 1 |
40 | 400 | 0 |
15 | 500 | 0 |
16 | 100 | 1 |
0 | 200 | 1 |
0 | 300 | 0 |
19 | 400 | 0 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you want the count of the distinct values then just tell SQL to do that instead.
count(distinct case when (VARIABLE3=1) then VARIABLE_2 end ) as VAR2_CNT
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It really help to start with posting your input data.
data have;
input var1-var3;
cards;
15 100 1
19 200 1
20 300 1
40 400 0
15 500 0
16 100 1
0 200 1
0 300 0
19 400 0
;
And your expected output data.
data want;
input sum_var1 cnt_var2;
cards;
144 3
;
That way others can provide tested code:
proc sql;
create table want as
select
sum(var1) as sum_var1
, count(distinct case when(var3=1) then var2 end) as cnt_var2
from have
;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much! I will make sure to include the input data to begin with next time!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Show the LOG with the code and all messages, notes, warning or errors. Copy the text from the log, on the forum open a text box using the </> icon that appears above the message window and paste the text.
The text box is important because it will preserve the formatting of the text and diagnostic characters. The main message windows will reformat text and appearance in the message window is hard to tell where any of the diagnostics actually appeared.