proc report - help with multiple variables in column headings

[WendyT]

SAS Folks-

I need your help again - I'm stuck in PROC REPORT.  We're using 9.4M2 on a 64-bit Windows server, remote login from 64-bit PC.

I have to make a report for laboratory data that has analysis variables (3 variables, text and numeric) going down the page (generally 20-30 rows), and samples across the page (at least one, can be many).  Each sample has two columns, a result value (numeric) and a remark (text). Each set of two columns has three identifiers I need to stack:  a laboratory id (text), a name (text) and a date (SAS datetime).

Using dataset CARS to demonstrate, I'll use Cylinders to stand in for my analysis variables going down, Model to stand in for my laboratory IDs, and Horsepower and Origin to stand in for my data, then generate the text and date to stack with Model.  The three 'stacking' items will always correspond exactly.

The code below makes the report, and I show the result for make=Isuzu in the first picture. 

I've been fiddling with the code, and can't seem to figure out the correct combination of how to define stack_date and stack_date, and put them into the column statement correctly to get the second report.  

Thanks for any help you can give me!

WendyT 

data cars ; set sashelp.cars (keep=make model cylinders horsepower origin) ;

  length stack_text $ 2 ;

    if make in('Isuzu', 'Jeep', 'Land Rover') ;

       stack_text=substr(model,2) ;

       stack_date= "&sysdate9."D + _N_ ;

    label stack_text= "Text Stack variable" ;

    label stack_date= "Date Stack variable" ;

    format stack_date DATE9. ;

run ;

options nobyline ;

%let outdir=&temp_output. ;

systask command "del ""&OUTDIR.\*.* "" ";

%let base=Cars_report ;

ods _all_ close ;

ods trace on ;

ODS html

  path="&OUTDIR\" (URL=NONE)

    body="body_&BASE..html" (TITLE="&BASE._OUTPUT")

    contents="contents_&BASE..html"  

    frame="frame_&BASE..html" ;

title "Title one text by #BYVAL(MAKE) " ;

title2 "Report run on &sysdate9. " ;

　

proc report data=cars list ;

  by make ;

    column cylinders model,(horsepower origin) ;

      define cylinders / group ID;

      define model / across ;

* DEFINE STACK_TEXT / ??? ;

* DEFINE STACK_DATE / ??? ;

      define model/across ;

      define horsepower / group missing;

      define origin / group missing ;

run ;

　

ods html close ;

run ;

[Ksharp · Accepted solution]

Try add two dummy variables and use NOZERO option.

data cars ; set sashelp.cars (keep=make model cylinders horsepower origin) ;
  length stack_text $ 2 ;
    if make in('Isuzu', 'Jeep', 'Land Rover') ;
       stack_text=substr(model,2) ;
       stack_date= "&sysdate9."D + _N_ ;
    label stack_text= "Text Stack variable" ;
    label stack_date= "Date Stack variable" ;
    format stack_date DATE9. ;
    
    
    if stack_text=:'As' then do;x='As';y='9-Nov-16';end;
     else if stack_text=:'Ro' then do;x='Ro';y='10-Nov-16';end;
     
     
run ;
options nobyline ;
proc report data=cars list nowd  ;
  by make ;
    column cylinders x,y,model,(horsepower origin) ;
      define cylinders / group ID;
       define x/ across 'Sample' nozero;
      define y/ across '' nozero;
      define model / across '' ;
* DEFINE STACK_TEXT / ??? ;
* DEFINE STACK_DATE / ??? ;
      define horsepower / group missing;
      define origin / group missing ;
run ;

[WendyT]

Thank you so much Ksharp!

You've saved me yet again.  🙂

Wendy