Easy example of proc report

[BETO]

HI I have a data set that looks like this

ID.           Date.               Device             description

A1.           03/01/15.          R150.              Supplier 1

a2.           03/02/15.          R 161.             Supplier2

b1.           03/03/15.            R152.            supplier3

i need it to look like this

                      03/01/15.           03/02/15.              03/03/15

supplier1.          1

supplier2                                       1

supplier3.                                                                     1

it counts each incidents by date

[art297]

An easy example of PROC TABULATE:

proc tabulate data=have;

  class date description;

  table description,date*n;

run;

[BETO]

hI Arthur,

Thank for response I ran the script  you provided and it only gives   Date from 04/06/15-04/09/15   the table. That has 04/01/15-04/09/15 that shows partial days... In addition how can I sort date  to show most current date first ... And last question how can I export the results to a excel tab ?  My output  added- name: table

label: table1

data name; report

path. Tabulate.report.table

thank you

[Jagadishkatam]

Alternatively

data have;

input ID$           Date$               Device $           description :$10.;

flag=1;

label=date;

cards;

A1.           03/01/15.          R150.              Supplier1

a2.           03/02/15.          R161.             Supplier2

b1.           03/03/15.            R152.            supplier3

;

proc transpose data=have out=trans(drop=_name_);

by description;

id date;

idlabel date;

var flag;

run;

Thanks,

Jag

[BETO]

HI Jagadishkatam,

thanks for response  I ran your  test code  and  the output  is

description. Col1.   col 2.    col3.   col4

supplier1.      1.          1.       1.         1

supplier2.       1.          1.       1.         1.

supplier3.         1.         1.       1.         1

I would like for it to look like this

description      04/01/15.          04/02/15.       04/03/15

supplier1.            23.                     15.             45

supplier2.             12.                      33.             22

supplier3.               0.                         47.          54

i need count of each incidents

thank you

[BrunoMueller]

Proc REPORT can do this using the GROUP and ACROSS usage for the variables, see sample code below.

data have;
  infile cards dlm=",";
  input
    id : $8.
    date : mmddyy8.
    device : $8.
    desc : $32.
  ;
  format date date9.;
cards;
A1,03/01/15,R150,Supplier1
A1,03/01/15,R151,Supplier4
A1,03/01/15,R171,Supplier4
a2,03/02/15,R161,Supplier2
b1,03/03/15,R152,Supplier3
;

proc report data=have;
  column desc date;
  define desc / group;
  define date / across;
run;

[RW9]

Whilst you can do what you want, examples others have posted, the question would be is that what you want to do?  For instance you have three dates there, what if its a year of data, you would then have 365 columns, what about 2 years etc.  Is a transposed table the best method to display the data?

Jagadishkatam - I wouldn't recommend using the data as an ID variable.   What happens for instance if you need to use that information, or if you want to do array processing etc.  Far better to have a set column name, COLx, and maybe have label as the date if needed. 

[art297 · Accepted solution]

I'm not sure what your secondary questions are. You'd have to provide some example data, and desired output, that more completely describes what you want to achieve.

That said, here is how you could get an exportable file, ordered however you'd like:

data have;

  informat id $3.;

  informat date mmddyy8.;

  format date mmddyy8.;

  informat device $8.;

  informat description $10.;

  input ID Date  Device description;

  cards;

A1           03/01/15          R150            Supplier1

a2           03/02/15          R161            Supplier2

a2           03/02/15          R161            Supplier2

b1           03/03/15          R152            supplier3

b1           03/09/15          R152            supplier3

;

proc tabulate data=have out=need;

  class date description;

  table description,date*n;

run;

proc sort data=need;

  by description date;

run;

proc transpose data=need out=want (drop=_name_);

  var n;

  by description;

  id date;

run;

[BETO]

Hi Arthur

on the proc tram pose how can I get the date to be sorted ascending /descending

it currently exports

03/01/15.    03/02/15.  03/09/15  03/03/15

i would like to look like

03/01/15.  03/02/15.   03/03/15.    03/09/15

in addition how can I add a border to the proc trampose since it's being export to excel?

thank you

[art297]

Easiest way to re-order the variables, at this point, would be to use a retain statement in a data step. e.g.:

data want;

  retain description _03_01_15 _03_02_15 _03_03_15 _03_09_15;

  set want;

run;

Not sure what you mean by border. Probably someone else can help if you can indicate exactly what your border to look like.

[Ksharp]

Use SQL to get such order .

data want;

  retain  _03_01_15 _03_02_15  _03_09_15 _03_03_15 0;

run;

proc sql;

select name into : list separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='HAVE'

    order by input(compress(name,,'kd'),mmddyy6.);

quit;

data want;

  retain description &list ;

  set want;

run;

For you second Q, use ODS TAGSETS.EXCELXP + style=  , not proc export .

Xia Keshan

[RW9]

Or, as mentioned have the variable names as COL1-COLx, and put the date in the label.  I really don't understand this need to have variable names as dates, what happens if you want to do something on those columns?

proc transpose data=need out=want (drop=_name_);

  var n;

  by description;

  idlabel date;

run;