Help using Base SAS procedures

Transpose / sum entries

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

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: 7,363

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=_Smiley Happy;

  by date;

  id id;

  var n;

run;

data want;

  retain date G B L;

  set want;

run;

View solution in original post


All Replies
Super User
Posts: 17,824

Re: Transpose / sum entries

Where does the 3/2/1 come from?

Trusted Advisor
Posts: 1,204

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: 7,363

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=_Smiley Happy;

  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: 7,363

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: 7,363

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: 9,681

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.

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

Discussion stats
  • 8 replies
  • 295 views
  • 3 likes
  • 5 in conversation