CODEWITHSUNDEEP
sas
o.h
o.h

Reputation: 1262

Creating vertical detail tables

I'm looking for a way to create vertical tables in SAS where the variables are each treated as rows (as opposed to each row being an observation).

For example lets say I have some data for a bunch of companies, some of which is more important than others. It is easy to make proc report spit out a summary table with a few variables like this:

Name Price Shares MarketCap
co1    $5    100    $500
co2    $1    100    $100
co3    $2    200    $400

What I want to do after this is print a page of detailed information for each company which is essentially a table with a column for the description and a column for the value (and maybe a third column for the calculation).

Company 1

   Location:   CA
        CEO:   Bob Johnson
   Industry:   Semiconductors

     Shares:   100
Share Price:   $5
 Market Cap:   $500

The only way I can think of to do this in SAS is to basically transpose everything, create a new character variable that has the label (Location, Stock Price, Etc) and a second character variable that has the value and then make a two column report BY company to get a page for each. This is messy since some of the values are numeric and others are character so to get them to display on one column requires creating a new character variable and filling it with text versions of the numeric variables.

I figure there has got to be an easier way to create a vertical table since there are so many easy ways to create the horizontal tables.

Upvotes: 1

Views: 1027

Answers (3)

Robert Penridge
Robert Penridge

Reputation: 8513

There is also this solution which is probably better for your needs.

First create a HTML file that will be used as a template. Wherever you want to put a value, use a macro variable as a placeholder like so:

<html>
<h1> My title is &title </h1><br>
Name: &name <br>
Value of Blah: &blah
</html>

Make it as attractive looking as you like.

Next create a macro that will import the HTML template, replace the placeholders with actual values and save the result to a new file:

/*****************************************************************************
**  PROGRAM: MACRO.RESOLVE_FILE.SAS
**
**  READS IN A FILE AND REPLACES ANY MACRO REFERENCES IN THE FILE WITH THE 
**  ACTUAL MACRO VALUES.  EG.  IF THE FILE WAS AN HTML FILE AND IT CONTAINED 
**  THE FOLLOWING HTML:
**
**    <TITLE>&HTML_TITLE</TITLE>
**
**  THEN THE PROGRAM WOULD READ THE FILE IN AND RESOLVE IT SO THAT THE OUTPUT
**  LOOKED LIKE THIS:
**
**    <TITLE>ROB</TITLE>
**  
**  ... WHEN THE MACRO VARIABLE "HTML_TITLE" EXISTED AND CONTAINED A VALUE OF 
**  "ROB".  THIS IS USEFUL WHEN YOU NEED TO CREATE "DYNAMIC" HTML FILES FROM 
**  SAS BUT DONT WANT TO DO IT FROM A DATASTEP USING PUT STATEMENTS.  DOING
**  IT THIS WAY IS MUCH CLEANER.
**
**  PARAMETERS: NONE
**
******************************************************************************
**  HISTORY:
**  1.0 MODIFIED: 22-JUL-2010  BY:RP
**  - CREATED. 
**  1.1 MODIFIED: 18-FEB-2011  BY:RP
**  - ADDED LRECL OF 32K TO STOP TRUNCATION
*****************************************************************************/
%macro resolve_file(iFileIn=, iFileOut=);
  data _null_;
    length line $32767;
    infile "&iFileIn" truncover lrecl=32767;
    file   "&iFileOut" lrecl=32767;
    input; 
    line = resolve(_infile_);
    len = length(line);
    put line $varying. len;
  run;
%mend;

Create some test data. Also create some commands to call the above macro and pass in the values from the dataset:

data mydata;
  attrib name length=$10 format=$10.    label='FirstName'
         blah length=6   format=comma6. label='SomeValue'
         cmd1  length=$1000
         cmd2  length=$1000
         ;

  title = 1; 
  name = "Rob" ; 
  blah = 1000; 
  cmd1 = cats('%let title=',title,';',
              '%let name=',name,';',
              '%let blah=',blah,';');
  cmd2 = cats('%resolve_file(iFileIn=c:\template.html, iFileOut=c:\result',title,'.html);');
  output;

  title = 2; 
  name = "Pete"; 
  blah = 100 ; 
  cmd1 = cats('%let title=',title,';',
              '%let name=',name,';',
              '%let blah=',blah,';');
  cmd2 = cats('%resolve_file(iFileIn=c:\template.html, iFileOut=c:\result',title,'.html);');
  output;
run;

Use call execute to run the cmd1 and cmd2 that we created in the prior dataset. We have to only execute call execute on 1 row at a time so that the correct macro variables are used so do it using a loop. First calculate the number of rows in your dataset using your preferred technique:

proc sql noprint;      
  select count(*) into :nobs from mydata;
quit;

Then iterate through the dataset executing the commands one at a time and building each row to a new file:

%macro publish;
  %local tmp;
  %do tmp = 1 %to &nobs;
    data _null_;
      set mydata(firstobs=&tmp obs=&tmp);
      call execute (cmd1);
      call execute (cmd2);
    run;
  %end;
%mend;
%publish;

That should do the trick.

Upvotes: 1

Robert Penridge
Robert Penridge

Reputation: 8513

How about one of these solutions instead then... Open a table in Bases SAS to view it. Go to View->Form View. That puts it in the layout you requested. It may not look exactly the way you want but it's a quick option.

The alternative is to write your own. Create a macro that takes a dataset and anything else you want to specify as parameters and display it using ODS, the put statement, or whatever other technique you would like.

I'm not aware of any other built in method in SAS to do this.

Upvotes: 0

Robert Penridge
Robert Penridge

Reputation: 8513

Perhaps I'm missing something but didn't you answer your own question? It should be as easy as:

Create some sample data. Be sure that every column has a format and label applied:

data mydata;
  attrib name length=$10 format=$10.    label='FirstName'
         blah length=6   format=comma6. label='SomeValue';

  bygroup = 1; name = "Rob" ; blah = 1000; output;
  bygroup = 2; name = "Pete"; blah = 100 ; output;
run;

Transpose the data to make it tall:

proc transpose data=mydata out=trans;
  by bygroup;
  var _all_;
run;

Print it Out:

data _null_;
  set trans2;
  by bygroup;

  if first.bygroup then do;
    put bygroup;
    put "------";
  end;
  put _label_ ":" value;
run;

Result:

1
------
FirstName :Rob
SomeValue :1,000
2
------
FirstName :P
SomeValue :100

Upvotes: 1

Related Questions