topic Re: proc trasnspose in SAS Programming

proc trasnspose

Raj00007 — Mon, 23 Nov 2020 12:40:17 GMT

i Have a data like this

Obs ACTUAL PREDICT COUNTRY REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH123456789101112131415161718192021222324252627282930

$1,802	$1,760	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1993	Jan
$1,864	$861	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1993	Feb
$1,464	$1,579	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1993	Mar
$763	$670	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1993	Apr
$1,244	$1,186	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1993	May
$1,039	$1,146	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1993	Jun
$1,428	$1,232	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1993	Jul
$956	$686	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1993	Aug
$1,426	$1,203	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1993	Sep
$1,330	$991	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1993	Oct
$936	$1,257	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1993	Nov
$1,024	$843	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1993	Dec
$276	$715	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1994	Jan
$565	$902	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1994	Feb
$143	$560	UK	EAST	COMMERCIAL	FURNITURE	SOFA	1	1994	Mar
$676	$1,215	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1994	Apr
$1,265	$551	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1994	May
$493	$1,016	UK	EAST	COMMERCIAL	FURNITURE	SOFA	2	1994	Jun
$841	$575	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1994	Jul
$522	$860	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1994	Aug
$1,480	$1,090	UK	EAST	COMMERCIAL	FURNITURE	SOFA	3	1994	Sep
$803	$1,079	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1994	Oct
$940	$351	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1994	Nov
$1,326	$1,590	UK	EAST	COMMERCIAL	FURNITURE	SOFA	4	1994	Dec
$503	$1,559	UK	EAST	COMMERCIAL	FURNITURE	BED	1	1993	Jan
$496	$472	UK	EAST	COMMERCIAL	FURNITURE	BED	1	1993	Feb
$950	$1,198	UK	EAST	COMMERCIAL	FURNITURE	BED	1	1993	Mar
$1,585	$1,452	UK	EAST	COMMERCIAL	FURNITURE	BED	2	1993	Apr
$345	$1,024	UK	EAST	COMMERCIAL	FURNITURE	BED	2	1993	May
$1,528	$730	UK	EAST	COMMERCIAL	FURNITURE	BED	2	1993	Jun

i want it to convert like this how?

YEAR QUARTER BED CHAIR DESK SOFA TABLE
1993 1 $33,486 $34,644 $37,159 $40,722 $39,126
1993 2 $38,460 $35,087 $35,516 $38,099 $37,662
1993 3 $37,339 $36,988 $36,902 $41,470 $31,784
1993 4 $40,093 $41,905 $37,902 $35,605 $36,505
1994 1 $32,602 $37,496 $35,850 $31,943 $37,100
1994 2 $35,234 $36,590 $35,475 $37,870 $37,267
1994 3 $38,428 $36,379 $35,644 $32,741 $35,433
1994 4 $39,928 $35,337 $37,410 $36,741 $29,478

i tried something like this, but it didn't worked.

proc sort data = sale out=sale1;
by year quarter;run;

proc transpose data = sale1 out=sale2;
by year quarter;
id product;
var Actual;
run; 
proc print;
run;

Re: proc trasnspose

PaigeMiller — Mon, 23 Nov 2020 12:43:53 GMT

Do you need the output as a table to place in a report or document? Or do you need the output as a SAS data set?

It would be very helpful if you can provide data in this format from now on. We can't use screen captures to produce a working data set that we can use when we are writing code.

Re: proc trasnspose

Kurt_Bremser — Mon, 23 Nov 2020 12:50:50 GMT

First, summarize:

proc summary data=have;
class year quarter product;
var actual;
output out=sum sum()=;
run;

then transpose

proc transpose
  data=sum
  out=want (drop=_:)
;
by year quarter;
id product;
var actual;
run;

Untested, for lack of usable data.

For tested code, supply example data in a working data step with datalines that creates your dataset.

Post the code into a window opened with the "little running man" button next to the one indicated:

Re: proc trasnspose

Kurt_Bremser — Mon, 23 Nov 2020 13:33:47 GMT

As a report:

proc report data=have;
column year quarter actual,product;
define year / group;
define quarter / group;
define product / "" across;
define actual / "" analysis sum;
run;

Re: Proc TRANSPOSE

RichardDeVen — Mon, 23 Nov 2020 13:25:10 GMT

Instead of a transposed data set you might consider creating a cross tabulation report.

The data appears to be very similar to sample data set SASHELP.PRDSALE so I will use that in the following example:

ods html file='sales.html' style=plateau;

title 'Actual sales, by year, all countries';
title2 'TABULATE';
proc tabulate data=sashelp.prdsale;
  class year quarter product;
  var actual;
  table year*quarter,product=''*actual=''*sum=''*f=dollar12./nocellmerge;
run;

title2 'REPORT';
proc report data=sashelp.prdsale;
  column year quarter actual,product;
  define year/group;
  define quarter/group;
  define product/' ' across;
  define actual/' ' format=dollar12.;
run;

ods html close;

Image of HTML output