topic Re: Easy way to comvert ds1 to ds2 in New SAS User

Easy way to comvert ds1 to ds2

Angel_Saenz — Thu, 07 Feb 2019 17:05:16 GMT

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

Re: Easy way to comvert ds1 to ds2

novinosrin — Thu, 07 Feb 2019 17:13:07 GMT

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

Re: Easy way to comvert ds1 to ds2

Angel_Saenz — Thu, 07 Feb 2019 17:22:02 GMT

I see PRODUCT A and PRODUCT B move to wrong way

I need both products have both incomes like:

PRODUCT A

incomeA incomeB

Re: Easy way to comvert ds1 to ds2

Angel_Saenz — Thu, 07 Feb 2019 17:23:19 GMT

ds2 coud be a report novinosrin

Re: Easy way to comvert ds1 to ds2

novinosrin — Thu, 07 Feb 2019 17:37:38 GMT

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

Re: Easy way to comvert ds1 to ds2

novinosrin — Thu, 07 Feb 2019 17:41:41 GMT

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	15.00	150.00
B	50.00	600.00
C	50.00	600.00

Re: Easy way to comvert ds1 to ds2

PaigeMiller — Thu, 07 Feb 2019 17:46:25 GMT

@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.

Re: Easy way to comvert ds1 to ds2

ballardw — Thu, 07 Feb 2019 18:47:31 GMT

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.