topic Re: Easy example of proc report in SAS Procedures

Easy example of proc report

BETO — Thu, 09 Apr 2015 21:53:11 GMT

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

Re: Easy example of proc report

art297 — Thu, 09 Apr 2015 23:29:21 GMT

An easy example of PROC TABULATE:

proc tabulate data=have;

class date description;

table description,date*n;

run;

Re: Easy example of proc report

Jagadishkatam — Fri, 10 Apr 2015 01:40:06 GMT

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

Re: Easy example of proc report

BrunoMueller — Fri, 10 Apr 2015 07:18:54 GMT

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;

Re: Easy example of proc report

RW9 — Fri, 10 Apr 2015 08:24:19 GMT

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.

Re: Easy example of proc report

BETO — Fri, 10 Apr 2015 17:09:40 GMT

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

Re: Easy example of proc report

BETO — Fri, 10 Apr 2015 17:18:52 GMT

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

Re: Easy example of proc report

art297 — Fri, 10 Apr 2015 17:53:38 GMT

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

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;

Re: Easy example of proc report

BETO — Thu, 30 Apr 2015 18:06:05 GMT

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

Re: Easy example of proc report

art297 — Thu, 30 Apr 2015 18:14:39 GMT

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.

Re: Easy example of proc report

Ksharp — Fri, 01 May 2015 06:57:15 GMT

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

Re: Easy example of proc report

RW9 — Fri, 01 May 2015 07:52:18 GMT

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;