```Date: Wed, 6 Jul 2005 17:46:10 +0000 Reply-To: toby dunn Sender: "SAS(r) Discussion" From: toby dunn Subject: Re: need help in solving this problem Comments: To: shankardasm@GMAIL.COM 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 Reply-To: shanky 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 > Reply-To: shanky > 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 ```

Back to: Top of message | Previous page | Main SAS-L page