topic Re: Calculating new columns from proc tabulate results in New SAS User

Calculating new columns from proc tabulate results

DThielemier — Wed, 14 Nov 2018 15:54:05 GMT

My raw data has four variables (Item number, Grouping, Part code, Quote).

Item number	Grouping	Part code	Quote
3328	Group A	A
3328	Group B	A
3328	Group C	A
3328	Group D	A
3328	Group E	A
3328	Group F	A	2197.98
3328	Group G	A
3328	Group H	A
3328	Group A	B
3328	Group B	B
3328	Group C	B
3328	Group D	B	1446
3328	Group E	B	1540.59
3328	Group F	B	1318.89
3328	Group G	B	1432.21
3328	Group H	B	1501.54
3328	Group A	C
3328	Group B	C
3328	Group C	C
3328	Group D	C
3328	Group E	C
3328	Group F	C	1729.77
3328	Group G	C
3328	Group H	C
3328	Group A	D
3328	Group B	D
3328	Group C	D
3328	Group D	D	1966
3328	Group E	D	1976.61
3328	Group F	D	1787
3328	Group G	D	1989.17
3328	Group H	D

I used a proc tabulate statement to display the quote data, by part code, for each combination of item number and grouping.

From here, I need to calculate some new variables. I need a new part code that is derived by subtracting part code B from part code D, for all item number and grouping combinations. Can I calculate new variables within proc tabulate?

I'd be fine with calculating the data directly from the raw data, but I don't know how to tell SAS "subtract quote value for Part code D, item number 3328, group C from part code B, item number 3328, group C".

Any advice would be greatly appreciated.

Re: Calculating new columns from proc tabulate results

Reeza — Wed, 14 Nov 2018 16:08:59 GMT

You cannot do this with PROC TABULATE. You may be able to with PROC REPORT, but I wouldn't recommend it at this stage. You can use the OUT= option on PROC TABULATE to push your results to a data set and then use that data set in a data step to do the actual calculations.

Re: Calculating new columns from proc tabulate results

PaigeMiller — Wed, 14 Nov 2018 16:20:42 GMT

Agreeing with @Reeza, there is no point in using a PROC TABULATE here. It doesn't do what you want, and it doesn't advance the process.

I'd be fine with calculating the data directly from the raw data, but I don't know how to tell SAS "subtract quote value for Part code D, item number 3328, group C from part code B, item number 3328, group C".

I think a PROC TRANSPOSE followed by doing the calculation you describe in a data step ought to work just fine.

Something like this:

UNTESTED CODE

proc transpose data=have out=have_t;
    by item_number grouping;
    var quote;
    id part_code;
run;

data want;
    set have_t;
    result=b-d;
run;

If you want actual TESTED code, you need to provide the data as actual SAS data step code.

Re: Calculating new columns from proc tabulate results

DThielemier — Wed, 14 Nov 2018 16:42:12 GMT

Transpose worked great! Thank you for the recommendation! I'm still pretty new to SAS, so thanks for helping me learn a new proc!