## Transpose / sum entries

Solved
Regular Contributor
Posts: 240

# Transpose / sum entries

HI  I need help with a Transpose Table

data is

DAte.       Id.       machine

08/1/14.    G.         A1

08/01/14.    B.        A2

08/01/14.      L.      A3

08/01/14.      G.       A4

08/01/14.       B.        A5

08/01/14.       G.       A6

i would like to see data output like this

Date                G.               B.               L

08/01/14.               3.                2.               1

Thanks

Accepted Solutions
Solution
‎09-10-2014 06:17 PM
PROC Star
Posts: 8,169

## Re: Transpose / sum entries

Since you want a dataset, expanding on stat@sas's code:

data have;

input Date \$ Id \$   machine \$;

datalines;

08/01/14   G    A1

08/01/14   B    A2

08/01/14   L    A3

08/01/14   G    A4

08/01/14   B    A5

08/01/14   G    A6

;

proc tabulate data=have out=need (keep=date id n);

class date id machine;

table date,id=' '*n=' ';

run;

proc transpose data=need out=want (drop=_;

by date;

id id;

var n;

run;

data want;

retain date G B L;

set want;

run;

All Replies
Super User
Posts: 23,789

## Re: Transpose / sum entries

Where does the 3/2/1 come from?

Posts: 1,270

## Re: Transpose / sum entries

data have;

input Date \$ Id \$   machine \$;

datalines;

08/01/14   G    A1

08/01/14   B    A2

08/01/14   L    A3

08/01/14   G    A4

08/01/14   B    A5

08/01/14   G    A6

;

proc tabulate data=have;

class date id machine;

table date,id=' '*n=' ';

run;

Solution
‎09-10-2014 06:17 PM
PROC Star
Posts: 8,169

## Re: Transpose / sum entries

Since you want a dataset, expanding on stat@sas's code:

data have;

input Date \$ Id \$   machine \$;

datalines;

08/01/14   G    A1

08/01/14   B    A2

08/01/14   L    A3

08/01/14   G    A4

08/01/14   B    A5

08/01/14   G    A6

;

proc tabulate data=have out=need (keep=date id n);

class date id machine;

table date,id=' '*n=' ';

run;

proc transpose data=need out=want (drop=_;

by date;

id id;

var n;

run;

data want;

retain date G B L;

set want;

run;

Regular Contributor
Posts: 240

## Re: Transpose / sum entries

Hi Arthur Tabacheck,

i have an additional question how can I auto append the "want" table to access table ? so once want is created it appends it to access

Thanks

PROC Star
Posts: 8,169

## Re: Transpose / sum entries

I don't know what you mean by 'access' or what you want the final file to look like.

Regular Contributor
Posts: 240

## Re: Transpose / sum entries

HI Arthur

i would like to append it to Access  its an office program Excel has extension of .XLS  Access has .mdb

PROC Star
Posts: 8,169

## Re: Transpose / sum entries

Append it to an existing access file, or just create an access file? If it is the latter, and your site licences SAS/Access for pc file formats, look up proc export in the documentation.

Super User
Posts: 10,788

## Re: Transpose / sum entries

ODBC + libname  .  check it at support.sas.com to see how to set up a ODBC connection with database .

🔒 This topic is solved and locked.