Hi Mark,

It took me a while to understand exactly what you really want. Especially I had hard time understand how the S1No, S2No, B1bx (I assume it was a typo of B1No?) B2No. Anyway, I've come up some code based on my intepretation and it resulted in almost what you've given below. You just need some more data manipulation, mostly classic data step techniques:

-------- data xx; input Prcode $ Prtype $ price status $ date :date. Sales stinvt; format date date.; cards; C02M S 112.00 N 02DEC2002 0 400 F02E S 112.00 N 02DEC2002 0 200 C02N S 112.00 N 02DEC2002 0 600 801A S 114.00 N 02DEC2002 0 230 D02N S 114.00 N 02DEC2002 0 600 F02E B 109.00 N 02DEC2002 0 100 D02O B 109.00 N 02DEC2002 0 500 D021 B 109.00 N 02DEC2002 0 570 A029 B 110.00 N 02DEC2002 0 500 C02M S 112.00 S 05DEC2002 200 200 A029 B 110.00 S 05DEC2002 200 300 902B B 110.00 N 07DEC2002 0 400 C02N B 110.00 N 07DEC2002 0 100 C02M S 112.00 D 07DEC2002 200 0 A029 B 110.00 D 07DEC2002 300 0 ;

proc sql; create table cnt as select a.date, a.price,b.prcode,b.status from xx a, xx b where a.date >= b.date and a.price=b.price group by a.date,a.price,b.prcode having max(status='D')=0 order by a.date,a.price,b.prcode ; create table cnt as select distinct date, price, count(distinct prcode) as cnt from cnt group by date,price ; create table yy as select date, prtype, price, sum(case when status='N' then stinvt else -sales end) as inst from xx group by date, prtype, price order by date, prtype, price ; data yy; merge yy cnt; by date price; run;

data yy1; set yy; by date prtype; idv=compress(trim(prtype)||put(price,best.)||'pr'); val=price; output; idv=compress(trim(prtype)||put(price,best.)||'qt'); val=inst; output; idv=compress(trim(prtype)||put(price,best.)||'cnt'); val=cnt; output; run;

proc transpose data=yy1 out=yy2 (drop=_name_); by date; id idv; var val; run;

proc print heading=v noobs; run;

B B S S B B 1 B B 1 S S 1 S S 1 1 1 0 1 1 1 1 1 1 1 1 1 d 0 0 9 1 1 0 1 1 2 1 1 4 a 9 9 c 0 0 c 2 2 c 4 4 c t p q n p q n p q n p q n e r t t r t t r t t r t t

02DEC02 109 1170 3 110 500 1 112 1200 3 114 830 2 05DEC02 . . . 110 -200 1 112 -200 3 . . . 07DEC02 . . . 110 200 2 112 -200 2 . . .

Now all you need to do is to fill in those missing values based on their previous record's value (LOCF). Also, for the qt variables, replace the current value with the previous value + current value,which makes:

b110qt s112qt 500 1200 300=500-200 1000=1200-200 500=300+200 800=1000-200

I leave these for you to finish. I hope my understanding to your requirement is correct.

Kind regards,

Ya Huang

On Fri, 26 Nov 2004 12:55:44 -0800, Mark Nloka <mdrnl@YAHOO.COM> wrote:

>SAS Users, > >Good afternoon and Happy Thanks Givings. I have the following >inventory dataset (an extract): > >Prcode Prtype price status date Sales stinvt >C02M S 112.00 N 02DEC2002 0 400 >F02E S 112.00 N 02DEC2002 0 200 >C02N S 112.00 N 02DEC2002 0 600 >801A S 114.00 N 02DEC2002 0 230 >D02N S 114.00 N 02DEC2002 0 600 >F02E B 109.00 N 02DEC2002 0 100 >D02O B 109.00 N 02DEC2002 0 500 >D021 B 109.00 N 02DEC2002 0 570 >A029 B 110.00 N 02DEC2002 0 500 >C02M S 112.00 S 05DEC2002 200 200 >A029 B 110.00 S 05DEC2002 200 300 >902B B 110.00 N 07DEC2002 0 400 >C02N B 110.00 N 07DEC2002 0 100 >C02M S 112.00 D 07DEC2002 200 0 >A029 B 110.00 D 07DEC2002 300 0 > >Note: N (items in stock); S (Sales); D (Out of stock: specific >product); Prtype - types of brand quality; Prcode - product code. > >I want to track inventory movements (in a dynamic way) as events occur >(inflow, sales and out of stock). Moreover, I want the following >dataset after each event grouped by date: > >date S1pr S1qt S1No S2pr S2qt S2No B1pr B1qt B1bx B2pr B2qt B2No >02DEC2002 112 1200 3 114 830 2 110 500 1 109 1170 3 >05DEC2002 112 1000 3 114 830 2 110 300 1 109 1170 3 >07DEC2002 112 800 2 114 830 2 110 500 2 109 1170 3 > >Efficiency of the SAS programme is crucial because the original >dataset contains over 700,000 observations. I have been looking at the >challenge from several angles but unfortuantely, I am yet to find a >suitable solution. Therefore, I turn to your assistance and guidance. > >Thank in advance and enjoy the holidays or weekend wherever you may >be. > >Mark