Help using Base SAS procedures

Easy example of proc report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Easy example of proc report

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


Accepted Solutions
Solution
‎04-10-2015 01:53 PM
PROC Star
Posts: 7,474

Re: Easy example of proc report

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;

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: Easy example of proc report

An easy example of PROC TABULATE:

proc tabulate data=have;

  class date description;

  table description,date*n;

run;

Regular Contributor
Posts: 240

Re: Easy example of proc report

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

Trusted Advisor
Posts: 1,137

Re: Easy example of proc report

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

Thanks,
Jag
Regular Contributor
Posts: 240

Re: Easy example of proc report

Posted in reply to Jagadishkatam

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

SAS Super FREQ
Posts: 708

Re: Easy example of proc report

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;
Super User
Super User
Posts: 7,955

Re: Easy example of proc report

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. 

Solution
‎04-10-2015 01:53 PM
PROC Star
Posts: 7,474

Re: Easy example of proc report

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;

Regular Contributor
Posts: 240

Re: Easy example of proc report

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

PROC Star
Posts: 7,474

Re: Easy example of proc report

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.

Super User
Posts: 10,028

Re: Easy example of proc report

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

Super User
Super User
Posts: 7,955

Re: Easy example of proc report

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 511 views
  • 0 likes
  • 6 in conversation