|
|
proc sql+count
Hi all,
I am submitting the following code ..i get wrong values for the count of actual
....
data try1;
input FAcility_RK ITEM_RK DEMAND_DT mmddyy10. ACTUAL PREDICT;
format DEMAND_DT date9.;
cards;
40 121 10/1/2010 . 0
40 234 10/1/2008 0 0
40 234 1/1/2009 0 0
40 234 4/1/2009 0 0
40 234 7/1/2009 0 0
40 234 10/1/2009 . 0
40 234 1/1/2010 . 0
40 234 4/1/2010 . 0
40 234 7/1/2010 . 0
40 234 10/1/2010 . 0
40 291 4/1/2009 6 6
40 291 7/1/2009 0 0
40 291 10/1/2009 . 0
40 291 1/1/2010 . 0
40 291 4/1/2010 . 0
40 291 7/1/2010 . 0
40 291 10/1/2010 . 0
40 301 10/1/2008 0 0
40 301 1/1/2009 0 0
40 301 4/1/2009 0 0
40 301 7/1/2009 0 0
;
run;
proc print;
run;
proc sql;
create table test78 as
select FAcility_RK,ITEM_RK,DEMAND_DT ,ACTUAL,PREDICT,count(ACTUAL)
as calc
from try1 group by FAcility_RK,ITEM_RK;
quit;
output is ..
FAcility_RK ITEM_RK DEMAND_DT ACTUAL PREDICT calc
40 121 10/1/2010 0 0
40 234 10/1/2009 0 4
40 234 1/1/2010 0 4
40 234 1/1/2009 0 0 4
40 234 10/1/2010 0 4
40 234 7/1/2010 0 4
40 234 7/1/2009 0 0 4
40 234 10/1/2008 0 0 4
40 234 4/1/2009 0 0 4
40 234 4/1/2010 0 4
40 291 1/1/2010 0 2
40 291 4/1/2010 0 2
40 291 10/1/2009 0 2
40 291 7/1/2009 0 0 2
40 291 4/1/2009 6 6 2(y do iget 2 instead of 1)
40 291 10/1/2010 0 2
40 291 7/1/2010 0 2
40 301 7/1/2009 0 0 4
40 301 4/1/2009 0 0 4
40 301 1/1/2009 0 0 4
40 301 10/1/2008 0 0 4
plz guide me where i am doing wrong.
Thanks all
|
|
0
|
|
|
|
Reply
|
 peesari.mahesh (114)
|
1/6/2010 10:14:05 AM |
|
Hi,
you get 2 as the value for calc as count(ACTUAL) is counting every non-missing value of ACTUAL.
In your case that is 6 and 0 (and not ".").
Regards,
Marianne
On Wed, 6 Jan 2010 05:14:05 -0500, SUBSCRIBE SAS-L Joe H. Smith
<peesari.mahesh@GMAIL.COM> wrote:
>Hi all,
>
>I am submitting the following code ..i get wrong values for the count of actual
>...
>
>data try1;
>input FAcility_RK ITEM_RK DEMAND_DT mmddyy10. ACTUAL PREDICT;
>format DEMAND_DT date9.;
>cards;
>40 121 10/1/2010 . 0
>40 234 10/1/2008 0 0
>40 234 1/1/2009 0 0
>40 234 4/1/2009 0 0
>40 234 7/1/2009 0 0
>40 234 10/1/2009 . 0
>40 234 1/1/2010 . 0
>40 234 4/1/2010 . 0
>40 234 7/1/2010 . 0
>40 234 10/1/2010 . 0
>40 291 4/1/2009 6 6
>40 291 7/1/2009 0 0
>40 291 10/1/2009 . 0
>40 291 1/1/2010 . 0
>40 291 4/1/2010 . 0
>40 291 7/1/2010 . 0
>40 291 10/1/2010 . 0
>40 301 10/1/2008 0 0
>40 301 1/1/2009 0 0
>40 301 4/1/2009 0 0
>40 301 7/1/2009 0 0
>;
>run;
>proc print;
>run;
>proc sql;
> create table test78 as
> select FAcility_RK,ITEM_RK,DEMAND_DT ,ACTUAL,PREDICT,count(ACTUAL)
>as calc
> from try1 group by FAcility_RK,ITEM_RK;
> quit;
>output is ..
>
>FAcility_RK ITEM_RK DEMAND_DT ACTUAL PREDICT calc
>40 121 10/1/2010 0 0
>40 234 10/1/2009 0 4
>40 234 1/1/2010 0 4
>40 234 1/1/2009 0 0 4
>40 234 10/1/2010 0 4
>40 234 7/1/2010 0 4
>40 234 7/1/2009 0 0 4
>40 234 10/1/2008 0 0 4
>40 234 4/1/2009 0 0 4
>40 234 4/1/2010 0 4
>40 291 1/1/2010 0 2
>40 291 4/1/2010 0 2
>40 291 10/1/2009 0 2
>40 291 7/1/2009 0 0 2
>40 291 4/1/2009 6 6 2(y do iget 2 instead of 1)
>40 291 10/1/2010 0 2
>40 291 7/1/2010 0 2
>40 301 7/1/2009 0 0 4
>40 301 4/1/2009 0 0 4
>40 301 1/1/2009 0 0 4
>40 301 10/1/2008 0 0 4
>
>plz guide me where i am doing wrong.
>
>Thanks all
|
|
|
0
|
|
|
|
Reply
|
 m.weires (5)
|
1/6/2010 1:44:23 PM
|
|
|
1 Replies
257 Views
(page loaded in 0.032 seconds)
Similiar Articles: proc sql+count - comp.soft-sys.sasHi all, I am submitting the following code ..i get wrong values for the count of actual .... data try1; input FAcility_RK ITEM_RK DEMAND_DT mm... Re: Equivalent of NODUPKEY in PROC SQL? - comp.soft-sys.sas ...Perhaps: Proc sql; create dataset2 as (select count(*) as count, var1, var2, var3 from dataset1 group by var1 var2 var3); alter table dataset2 drop count; quit;run ... Termination due to Floating Point Exception with Proc NLMixed for ...proc sql+count - comp.soft-sys.sas Termination due to Floating Point Exception with Proc NLMixed for ... Termination due to Floating Point Exception with Proc NLMixed for ... PROC SQL and round-function - comp.soft-sys.sasPerhaps: Proc sql; create dataset2 as (select count(*) as ... soft-sys.stat.spss Re: Equivalent of NODUPKEY in PROC SQL? - comp.soft-sys.sas ... SAS equivalent function ... PROC SQL: Concatenating values of several columns into several ...proc append to combine multiple tables on Unix - comp.soft-sys.sas ... proc sql ; select table_names into ... macro appendn ; proc sql; select count(table_names) , table ... How to create Population- Place report using SAS Procs/Proc SQL ...Hi, I have following data in dataset and I need to create a count report for Population- Place combination. Population Place ... Weird issue in DHMS function when used in Proc SQL - comp.soft-sys ...proc sql+count - comp.soft-sys.sas Weird issue in DHMS function when used in Proc SQL - comp.soft-sys ... Below is code used proc sql; CREATE TABLE temp1 AS SELECT A.*... ... Merge data sets with Proc SQL or a data set - comp.soft-sys.sas ...I am trying to fit a count data set to zero-inflated Negative Binomial model using Proc ... In addition, the other data sets fit ... proc sql+count - comp.soft-sys.sas ... Converting a datetime field to a date without using datepart() in ...Hi, Could I have some advice please as I need to convert a datetime field to a date but I cannot use datepart() and need to use PROC SQL. Woul... proc append to combine multiple tables on Unix - comp.soft-sys.sas ...rsubmit; proc sql ; Select table_names into : tbl_nm separated by ... hi, this will work for you: %macro appendn ; proc sql; select count(table_names ... Fun with PROC SQL Summary Functions - sasCommunityThe SQL procedure is a wonderful tool for summarizing (or aggregating) data. It provides a number of useful summary (or aggregate) functions to help perform ... The SQL Procedure: PROC SQL Statement - SAS Customer Support ...sets the maximum number of observations that must be in a table before the SQL procedure considers to optimize the PUT function to 2 63-1, or approximately 9.2 quintillion. 7/29/2012 7:38:22 AM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
17 followers. 