Easy way to comvert ds1 to ds2

[Angel_Saenz]

Hi, I know some steps to do it but I want to find best.

I need to convert ds1 to ds2:

ds1

product	INCOME 1	INCOME 2
A	10	100
A	20	200
B	40	300
B	70	600
B	40	900

ds2

PRODUCT A	PRODUCT B
INCOME 1 AVG	INCOME 2 AVG	INCOME 1 AVG	INCOME 2 AVG
15	150	50	600

is it possible with only one step? proc freq? summary? thanks

[novinosrin]

is ds2 a dataset or report? or in other words are you looking to create a report or dataset?

[Angel_Saenz]

ds2 coud be a report novinosrin

[Angel_Saenz]

I see PRODUCT A and PRODUCT B move to wrong way

I need both products have both incomes like:

    PRODUCT A

incomeA incomeB

[novinosrin · Accepted solution]

I am wary of your design to just make it wide all in one and not have clear cross tabs/contingency table to make it meaningful

What i mean is

data have;
input product $	INCOME1	INCOME2;
cards;
A	10	100
A	20	200
B	40	300
B	70	600
B	40	900
C	40	300
C	70	600
C	40	900
;
proc means data=have nway noprint;
class product;
var income:;
output out=w(drop=_:) mean=;
run;

proc transpose data=w out=w1;
var income:;
id product;
run;

would give you

_NAME_ A B C 
INCOME1 15 50 50 
INCOME2 150 600 600 

However in your sample output one row wide , i am not quite getting how you make sense of it

[novinosrin]

Also, a tabulate is prolly not much different either

data have;
input product $	INCOME1	INCOME2;
cards;
A	10	100
A	20	200
B	40	300
B	70	600
B	40	900
C	40	300
C	70	600
C	40	900
;

proc tabulate data=have;
class product;
var income:;
table product,income:*mean;
run;

The SAS System

	INCOME1	INCOME2
	Mean	Mean
product	15.00	150.00
A
B	50.00	600.00
C	50.00	600.00

[ballardw]

See if this makes you happier:

data have;
input product $	INCOME1	INCOME2;
cards;
A	10	100
A	20	200
B	40	300
B	70	600
B	40	900
C	40	300
C	70	600
C	40	900
;
run;

proc tabulate data=have;
   class product;
   var income1 income2;
   table mean='',product=''*(income1 income2)
      / row=float
   ;
run;

The mean='' says the row will be a mean value and the ='' suppresses the default label of mean.

The table option row=float removes an empty column created by the suppressed mean label.

You can control the format of the calculated mean by adding *f=<pick your format> after the mean='' part. Example

mean=''*f= f5.0 would display as 5 digits without any decimal portion.

[PaigeMiller]

---

@Angel_Saenz wrote:

Hi, I know some steps to do it but I want to find best.

I need to convert ds1 to ds2:

ds1

product	INCOME 1	INCOME 2
A	10	100
A	20	200
B	40	300
B	70	600
B	40	900

ds2

PRODUCT A	PRODUCT B
INCOME 1 AVG	INCOME 2 AVG	INCOME 1 AVG	INCOME 2 AVG
15	150	50	600

is it possible with only one step? proc freq? summary? thanks

---

Why do you have to do it in one step? Why do you have to use PROC FREQ or PROC SUMMARY? 

Also, I don't really understand your desired output, it seems like under Product A is Income 1, and then under Product B is Income 2, Income 1 and another Income 2.

if that's an error, and both Income 1 and Income 2 should be under both Product A and Product B, then PROC REPORT ought to do this easily.