i Have a data like this
$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;
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;
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.
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:
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.