BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
art297
Opal | Level 21

An easy example of PROC TABULATE:

proc tabulate data=have;

  class date description;

  table description,date*n;

run;

BETO
Fluorite | Level 6

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
Amethyst | Level 16

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
BETO
Fluorite | Level 6

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
SAS Super FREQ

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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