/**********************************************************************/


create noprompt data=toys.sales colvar rowvar pagevar 
       cname=product rname=quarter pname=company vkey=row3 
       okey=col1 fbkey=row3 sbkey=col1 idname=sales

   where

DATA=     specifies the name of the data set, TOYS.SALES
COLVAR    specifies that a special variable will be output; 
          also see CNAME= 
ROWVAR    specifies that a special variable will be output; 
          also see RNAME= 
PAGEVAR   specifies that a special variable will be output; 
          also see PNAME= 
CNAME=    names the special variable, PRODUCT, that will contain 
          a customized column name in the spreadsheet
RNAME=    names the special variable, QUARTER, that will contain 
          a customized row name in the spreadsheet
PNAME=    names the special variable, COMPANY, that will contain 
          a customized page name in the spreadsheet
VKEY=     names the spreadsheet row, ROW3, that will map to the 
          RNAME= special variable
OKEY=     names the spreadsheet column, COL1, that will map to 
          the CNAME= special variable
FBKEY=    names the spreadsheet's first dimension, ROW3, that 
          will map to the PNAME= special variable
SBKEY=    names the spreadsheet's second dimension, COL1, that 
          will map to the PNAME= special variable
IDNAME=   names the variable, SALES, that contains spreadsheet 
          cell values


/**********************************************************************/


fetch noprompt data=toys.sales vr=quarter matchvr=row3 
      id=product matchid=col1 by=company fmatchby=row3 
      smatchby=col1 varlist=sales

where

DATA=     specifies the name of the data set, TOYS.SALES
VR=       specifies the name of a special variable (QUARTER) that 
          contains a customized name in the spreadsheet
MATCHVR=  specifies the row for mapping the special variable 
          named in VR=
ID=       specifies the name of a special variable (PRODUCT) that 
          contains a customized name in the spreadsheet
MATCHID=  specifies the column for mapping the special variable 
          named in ID=
BY=       specifies the name of a special variable (COMPANY) that 
          contains a customized name in the spreadsheet
FMATCHBY= specifies the row for mapping the special variable named 
          in BY=
SMATCHBY= specifies the column for mapping the special variable 
          named in BY=
VARLIST=  specifies the name of the variable that contains 
          spreadsheet cell values


/**********************************************************************/


libname rikbear 'rikbear';
libname jefco   'jefco';
libname toysinc 'toysinc';

proc sql;
     create view toysinc.sales as
     select * from rikbear.sales
     union
     select * from jefco.sales
     order by company;
     quit;


/**********************************************************************/


if _status_='I' then call execcmd
   ('fetch noprompt data=toysinc.sales contype=replace 
    vr=quarter matchvr=row3 id=product matchid=col1 
    by=company fmatchby=row3 smatchby=col1 varlist=sales');

subset rows row5--row8;
subset cols col2--col5;

col6=sum(of col2--col5);
row10=sum(of row5--row8);
col6.row10=sum(col6.row5,col6.row6,col6.row7,col6.row8);


/**********************************************************************/