topic Re: Pivot Dataset in SAS Programming

Pivot Dataset

Q1983 — Tue, 12 Jan 2021 22:53:35 GMT

DATA HAVE;

LENGTH MONTH_ $5. CATGY DESCR $25. ;

INPUT CATGY MONTH_ TOT_CNT DESCR DESCR_PCT MONTHSUM;

DATALINES;

CURRENT JAN20 33 STAYED 98 1

CURRENT JAN20 33 ROLLED 2 1

CURRENT FEB20 25 STAYED 2 2

CURRENT FEB20 25 ROLLED 4 2

DEL_30 JAN20 10 STAYED 3 3

DEL_30 JAN20 10 ROLLED 1 3

DEL_30 FEB20 7 STAYED 2 4

DEL_30 FEB20 7 ROLLED 1 4

;

RUN;

PROC SQL;

CREATE TABLE HAVE2 AS

SELECT CATGY,MONTH_,TOT_CNT,DESCR,DESCR_PCT, MONTHSUM

FROM HAVE

;QUIT;

/*Initial output is here*/

CATGY MONTH_ TOT_CNT DESCR DESCR_PCT MONTHSUM

CURRENT JAN20 33 STAYED 98 1

CURRENT JAN20 33 ROLLED 2 1

CURRENT FEB20 25 STAYED 2 2

CURRENT FEB20 25 ROLLED 4 2

DEL_30 JAN20 10 STAYED 3 3

DEL_30 JAN20 10 ROLLED 1 3

DEL_30 FEB20 7 STAYED 2 4

DEL_30 FEB20 7 ROLLED 1 4

Monthsum appears because I need to display based on CATGY and Month

The desired output is in the attached spreadsheet because its almost impossible to paste it with the desired format directly

I am trying to determine if this can be done with proc transpose, proc tabulate or proc report

Re: Pivot Dataset

Reeza — Tue, 12 Jan 2021 23:34:37 GMT

You want the row labels repeated each time like that?
If you're flexible on that PROC TABULATE will work, otherwise I think you may be stuck with a manual version to get something like that.

Re: Pivot Dataset

PGStats — Wed, 13 Jan 2021 04:32:11 GMT

Is this close enough?

data temp;
set have; by catgy month_ notsorted;
if first.month_ then do;
    desc = "TOT_CNT";
    value = tot_cnt;
    output;
    end;
desc = descr;
value = descr_pct;
output;
run;

proc tabulate data=temp format=best. order=data;
class catgy month_ desc;
var value;
table catgy=""*desc="", month_=""*value=""*mean="";
run;

Re: Pivot Dataset

Tom — Wed, 13 Jan 2021 06:00:09 GMT

Note that it is probably a lot faster to just type in the 12 numbers than it is to create and attach a spreadsheet.

                  JAN20                  FEB20
        TOT_CNT      33        TOT_CNT      25
CURRENT STAYED       98        STAYED        2
        ROLLED        2        ROLLED        4
        TOT_CNT      10        TOT_CNT       7
DEL_30  STAYED        3        STAYED        2
        ROLLED        1        ROLLED        1

Why did you post the SQL code? It is not doing anything.

If you want to display both the values of MONTH_SUM and TOT_CNT in the same field of a report then put them into the same variable in the SAS dataset.

data have;
 length catgy $25 month_ $5 tot_cnt 8 descr $25 descr_pct monthsum 8 ;
 input catgy -- monthsum;
datalines;
CURRENT JAN20 33 STAYED 98 1
CURRENT JAN20 33 ROLLED 2 1
CURRENT FEB20 25 STAYED 2 2
CURRENT FEB20 25 ROLLED 4 2
DEL_30 JAN20 10 STAYED 3 3
DEL_30 JAN20 10 ROLLED 1 3
DEL_30 FEB20 7 STAYED 2 4
DEL_30 FEB20 7 ROLLED 1 4
;

data have2 ;
  set have;
  by catgy month_ notsorted;
  stat = monthsum;
  output ;
  if last.month_ then do;
    descr = 'TOT_CNT';
    stat = tot_cnt;
    output;
  end;
run;

Now you can make a report from that new dataset.

proc report data=have2 ;
  column catgy descr stat,month_ ;
  define catgy / group ;
  define descr / group ;
  define month_ / across ' ' order=data;
  define stat / sum ' ';
run;

 catgy                      descr                          JAN20      FEB20
  CURRENT                    ROLLED                             1          2
                             STAYED                             1          2
                             TOT_CNT                           33         25
  DEL_30                     ROLLED                             3          4
                             STAYED                             3          4
                             TOT_CNT                           10          7