Date: Wed, 6 Jul 2005 17:46:10 +0000
Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby dunn <tobydunn@HOTMAIL.COM>
Subject: Re: need help in solving this problem
In-Reply-To: <1120659976.607218.303470@g49g2000cwa.googlegroups.com>
Content-Type: text/plain; format=flowed
Shanky,
In the future you might want to ocnsider giving a little more data to work
with, so I expanded your input dataset a little.
data one ;
infile cards dsd dlm = ' ' ;
input StoreNo ProductNo $ Date $11. TransType $ NumOfUnits UnitPrice ;
Cards ;
1 x322 02/05/2003 B 12 125
1 x344 03/10/2003 B 10 200
1 x322 02/10/2003 B 20 150
1 x322 02/05/2003 S 12 125
2 x432 07/28/2003 S 6 65
2 x322 02/05/2003 B 12 125
2 x344 03/10/2003 B 10 200
;
run ;
>Problem 1: I have to create a table for displaying following
>information: Listing by store the store, products bought, total worth
>of products bought.
proc sort
data = one ;
by StoreNo ProductNo ;
run ;
Data List1 (keep = StoreNo ProductNo TotalUnitsBought TotalCost) ;
set one ;
by StoreNo ProductNo ;
Where TransType = 'B' ;
retain TransCost ;
TotalUnitsBought + NumOfUnits ;
TransCost = NumOfUnits * UnitPrice ;
TotalCost + TransCost ;
If Last.ProductNo then do ;
output ;
TotalUnitsBought = 0 ;
TotalCost = 0 ;
end ;
Run ;
proc print
data = List1 ;
run ;
>Problem 2: the products can be sold only after they are bought in a
>particular store. I would like to know how i can clean the data and
>output the errors.
Question here are you only concerned about the store having the product
before they sell it or do you need to work with FIFO? Also working
throughthe logic in my head what are you going to output when the case is
that the number of units on hand is less then the amount sold. Which is
wrong the amount you bought or the amount you sold. What do you want
outputted to the file or report?
>Problem 3: Proc freq will show which products are bought across all
>stores. I would like to know can i get the same information using 1)
>any other "PROC" and 2) only using DATA step and no "proc"
Uhhh I didn't do a data step solution as I think anything but maybe hash
solution would require atleast a proc sort. There maybe a solution using
arrays but that would get real ugly really fast in the way of house keeping
and possibly very very slow with a large number of store and product
combinations.
A proc solution:
Proc SQL ;
select count(distinct StoreNo) into : StoreCnt
from one ;
select ProductNo
from (select distinct StoreNo , ProductNo
from one )
group by ProductNo
having count(ProductNo) = &storecnt ;
quit ;
>Problem 4: I want to output a dataset showing for each store: the
>earliest purchase date, earliest sale date and total profit for each
>product.
Proc SQL ;
create table maxmin as
select StoreNo , ProductNo , MinDate , MaxDate ,
sum(Dollars) as dollars ,TransType
from (select StoreNo , ProductNo , Min(Date) as MinDate ,
Max(Date) as MaxDate , (NumOfUnits * UnitPrice) as
Dollars ,
TransType
from one
group by StoreNo , ProductNo )
group by StoreNo , ProductNo , TransType ;
quit ;
Data list4 (drop = Cost Revenue TransType Dollars) ;
set maxmin ;
by StoreNo ProductNo ;
if TransType = 'B' then Cost + Dollars ;
else Revenue + Dollars ;
if last.ProductNo then do ;
Profit = Revenue - Cost ;
output ;
Cost = 0 ;
Revenue = 0 ;
end ;
run ;
Proc print ;
run ;
>Problem 5: a macro for each store to show information across products.
What information do you want? Also do you want one macro that can have a
parameter with storeno passed or do you want it to figure that out too, or
do you want a seoarate macro for each store (which would be silly and defeat
the purpose of using a macro to begin with). Basically you will have to
flesh this out more before I think anyone will take a stab at it.
Toby Dunn
From: shanky <shankardasm@GMAIL.COM>
Reply-To: shanky <shankardasm@GMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: need help in solving this problem
Date: Wed, 6 Jul 2005 07:26:16 -0700
Toby Dunn:
Thanks for the reply. The data set will look like this:
store# Product# date PRodbuyorsell #ofunitstransacted
unitprice
1 x322 02/05/2003 B 12 125
2 x432 07/28/2003 S 6 65
Here is how i have proceeded:
For problem #1:
data product;
infile'c;\rawdata\product.dat';
input store $2 product_no $4 transdate mmddyy10. prodbuyorsell $1
unitstransacted unitprice;
totalprice= unitstransacted*unitprice;
proc tabulate data=totalbought;
where prodbuyorsell in ('B');
class store productbuyorsell;
var totalprice;
table product_no all prodbuyorsell*totalprice all;
run;
I want my results to show listing by store, the total # of products
bought and total amount spent in buying the products.
For the problem #3to find out products bought in all stores i have
solved using
data prodinfo;
proc freq data=product;
where prodbuyorsell='B';
tables product_no;
run;
Another method to get the same information as in problem 3 using the
proc mean step is:
data prodinfo;
proc sort data=product;
where prodbuyorsell='B';
by store;
proc means data=product;
where prodbuyorsell='B';
by store;
var product-no;
run;
Now to get same information as in problem 3 using data step i have
tried using the following program but i do not know
how to show the total products bought.
data_null_;
infile'C:\';
input input store $2 product_no $4 transdate mmddyy10. prodbuyorsell $1
unitstransacted unitprice;
where prodbuyorsell= 'B';
File'C:\rawdata\prodinfo' print;
title;
put @5'product buying report for 'store'bought'unitstransacted
'products of ' prod_no;
put _page_;
run;
for problem 4: I am stuck with finding out how to come up with the
earliest purchase date and sale date for
each product and the profit.
data prodinfo;
proc sort data=product;
by store product_no;
proc report data=product;
column store product_no earlypurchasedate earlysaledate;
run;
Also, i would appreciate if someone can help me with how to proceed
with the macro for finding information across products for a particular
store.
The output dataset i am looking for is the sas output form.
thanks in advance;
Shanks
toby dunn wrote:
> Shanks,
>
> No offense but his sounds an awful lot like a homework question.
>
> Now You will need to look into Proc Report and Data _null_, By group
> processing and/or class statement, and finally my advice is if you don't
how
> to do the afore mentioned then forget the macro as it will cause your
more
> headachs than you have asprin.
>
> Now having said that if you want help, then help us help by providing a
> small sample datasetand what you expect the results to be.
>
> Also would you like that in generic SAS output form, RTF, PDF, HTML,
Excel
> file, Access file?
>
>
>
> Toby Dunn
>
>
>
>
> From: shanky <shankardasm@GMAIL.COM>
> Reply-To: shanky <shankardasm@GMAIL.COM>
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: need help in solving this problem
> Date: Tue, 5 Jul 2005 21:28:48 -0700
>
>
> Hello everyone:
>
> Situation: A department store is having different products. The input
> includes store number, product number, whether the product is brought
> or sold, date of transaction in mmddyy10 format, unit price and # of
> units transacted.
>
> Problem 1: I have to create a table for displaying following
> information: Listing by store the store, products bought, total worth
> of products bought.
>
> Problem 2: the products can be sold only after they are bought in a
> particular store. I would like to know how i can clean the data and
> output the errors.
>
> Problem 3: Proc freq will show which products are bought across all
> stores. I would like to know can i get the same information using 1)
> any other "PROC" and 2) only using DATA step and no "proc"
>
> Problem 4: I want to output a dataset showing for each store: the
> earliest purchase date, earliest sale date and total profit for each
> product.
>
> Problem 5: a macro for each store to show information across products.
>
> Thanks in advance;
>
> Shanks
