Hi Everyone

I am stumped with a request and I am sure that somebody must have an answer for me.

I need to do something like the following, which is just for illustration.

TABLE A
Product POS_SOLD
apples $1.00
apples $1.25
pears $1.10


TABLE B
Product ACT_SOLD
apples $2.00
apples $1.00
pears $1.10
pears $1.10
pears $3.00

I need to create a report that shows how many times a product in Table B was sold at each value in TABLE A.

So in this example, I would need to produce these numbers..

We sold 2 apples. We used the $1.00 value 50% of the time and the $1.25 value 0% of the time.
We sold 3 pears. We used the $1.10 value 66% of the time.


The values in Table A are dynamic of course. So there could be 100 values per fruit or only 1.

For some reason I am having trouble wrapping my head around this one.

Thanks in advance.

Jodye

In your limited example, I see know way to know how many as relates to price per each except that you state as much. Does your real data have that available. Such as 5 items cost 5 dollars so price per item could be then 1, of course what if you have 5 items at 5.75, does this mean 2 at 1 and 3 at 1.25?

Hi Leah

Thanks for responding, but, I do not follow your question. Table B shows actual sales and Table A shows possible values.

I want to know how many actual sales were made for each fruit at each value in Table A. Table B shows that I sold 3 pears and 2 of them were $1.10 so that means I hit $1.10 2/3 (66%) of the time. I need to know how many sales were made at 1.10$ because that is in Table A.

Please let me know if there is something I can add to make it more clear.

EDIT: oh wait I see what you mean. For this example assume that each individual record in Table B represents one item. Pretty expensive fruit huh. So I can simply count the number of hits at each price.

Thanks

Jodye

So your Master file description of TABLEA has two key fields (the productname and the price)
and every product_price from TABLE B is in the TABLE A.

The solution is rather easy

TABLE FILE TAB_A
COUNT COMPUTE RECORDS/I3=1;
BY PRODUCT
BY PRICE
END

You get a report like

product price count
apples  $1.00    1
apples  $2.00    1
pears   $1.10    2
pears   $3.00    1    

and you can calculate the percentage of that count too.

If the $2.00 apples and $3.00 pears are not in table A you can join the two tables with an inner join and then you only get the products from your table A counted.

This is how I would do it.

1. Read table B and create a hold file with the total counts by product and by product by act_sold.

SET ASNAMES = ON

TABLE FILE B
SUM
CNT.PRODUCT/I9 AS 'TOTPRODUCTCNT'
BY PRODUCT
SUM
CNT.PRODUCT/I9 AS 'TOTACTSOLDCNT'
BY PRODUCT
BY ACT_SOLD
ON TABLE HOLD AS HOLD1
END

SET ALL = PASS

2. JOIN PRODUCT AND POS_SOLD IN TABLEA TO
PRODUCT AND ACT_SOLD IN HOLD1 AS JOIN1

3. Using a DEFINE FILE TABLEA create your percentage field.

4 Using a TABLE FILE TABLEA print your fields.

Hope this helps