[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    StackOverflow SAS: SAS Proc Report Specific Setup (datastep and traspose solutions)
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-09-27 20:22:08
Message-ID: 4912464445155854.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

StackOverflow SAS: SAS Proc Report Specific Setup (datastep and traspose solutions)

    TWO SOLUTIONS

    WORKING CODE
      SAS/WPS

     1.  * this sets subsequent names to blank;
         data want(drop=idx a c d);
          retain idx 1 nam b bog hog log '       ';
          array ogs[3] $8 bog hog log;
          set have;
            by b notsorted;
            ogs[idx] =d;
            if mod(_n_,9)=1  then nam=a;
            idx=idx+1;
            if last.b then do;
               output;
               idx=1;
               nam='';
            end;

     2.  * this does not set subsequent names to blank;

         proc transpose data=have out=want(drop=d);
         by a b;
         id c;
         var d;
         run;quit;


HAVE

  WORK.HAVE total obs=18                            RULES (roll Harry Hermione and Ron)

   Obs     A      B            C     D                 Bog       Hog     Log

     1    Bill    Harry       Bog    The
     2    Bill    Harry       Hog    Quick
     3    Bill    Harry       Log    Brown            The      Quick    Brown  (one ob out)

     4    Bill    Hermione    Bog    Fox
     5    Bill    Hermione    Hog    Jumps
     6    Bill    Hermione    Log    Over             Fox      Jumps    Over

     7    Bill    Ron         Bog    The
     8    Bill    Ron         Hog    Lazy
     9    Bill    Ron         Log    Dogs

    10    Ted     Harry       Bog    Peter
    11    Ted     Harry       Hog    Piper
    12    Ted     Harry       Log    Picked
    13    Ted     Hermione    Bog    A
    14    Ted     Hermione    Hog    Powerful
    15    Ted     Hermione    Log    Peck
    16    Ted     Ron         Bog    Of
    17    Ted     Ron         Hog    Picked
    18    Ted     Ron         Log    Peppers

WANT

   WORK.WANT total obs=6

   Obs    NAM     B          BOG      HOG        LOG

    1     Bill    Harry      The      Quick      Brown
    2             Hermion    Fox      Jumps      Over
    3             Ron        The      Lazy       Dogs

    4     Ted     Harry      Peter    Piper      Picked
    5             Hermion    A        Powerfu    Peck
    6             Ron        Of       Picked     Peppers

*                _              _       _
 _ __ ___   __ _| | _____    __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \  / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/ | (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|  \__,_|\__,_|\__\__,_|

;

data have;
input A$ B$ C$ D$;
cards4;
Bill Harry Bog The
Bill Harry Hog Quick
Bill Harry Log Brown
Bill Hermione Bog Fox
Bill Hermione Hog Jumps
Bill Hermione Log Over
Bill Ron Bog The
Bill Ron Hog Lazy
Bill Ron Log Dogs
Ted Harry Bog Peter
Ted Harry Hog Piper
Ted Harry Log Picked
Ted Hermione Bog A
Ted Hermione Hog Powerful
Ted Hermione Log Peck
Ted Ron Bog Of
Ted Ron Hog Picked
Ted Ron Log Peppers
;;;;
run;quit;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;


%utl_submit_wps64('
libname wrk "%sysfunc(pathname(work))";
data wrk.want(drop=idx a c d);
 retain idx 1 nam b bog hog log "       ";
 array ogs[3] $8 bog hog log;
 set wrk.have;
   by b notsorted;
   ogs[idx] =d;
   if mod(_n_,9)=1  then nam=a;
   idx=idx+1;
   if last.b then do;
      output;
      idx=1;
      nam="";
   end;
run;quit;
proc transpose data=wrk.have out=wrk.wantxpo(drop=_name_);
by a b;
id c;
var d;
run;quit;
');
[prev in list] [next in list] [prev in thread] [next in thread]