Help using Base SAS procedures

transpose dates into COL and keep format

Reply
Super Contributor
Posts: 395

transpose dates into COL and keep format

How can I transpose dates from a row to columns and keep the date format as the Column names.. here's what I mean..

HAVE:

VAR1    VAR2    Var3    Dates

Sales     Tor        45      2000M01

Sales     Tor        56      2000M02

....          .....          ....     ....

Sales     Tor        22      2013M10

Prices    Tor      67854   2000M02

....

Prices     Van    64678    2000M01

Prices     Van    84763    2013M10

WANT:

VAR1     VAR2     Jan.2000    Feb.2000    ....     Oct.2013  ....                    

Sales      Tor               45               56                         22                   

Prices     Tor                               67854

Prices     Van           64678                                     84763                                                                      

Thanks

PROC Star
Posts: 7,356

Re: transpose dates into COL and keep format

If you don't really need the period within the variable name (which you could only use if you were to include the validvarname=any option and, IMHO, isn't a good thing to do anyway), you could just include a format statement in proc transpose .. using the YYMONw. format .. var1 and var2 as by variables .. var3 as the var variable .. and dates as the id variable.

Regular Contributor
Posts: 213

Re: transpose dates into COL and keep format

See if this works for you

data have;

    length Category $10 Model $5 unit 8 date_c $7 date 8;

    input Category $ Model $ unit date_c $;

    date = input(compress(date_c,'M'),yymmn6.);

    format date monyy7.;

datalines;

Sales Tor 45 2000M01

Sales Tor 56 2000M02

Sales Tor 22 2013M10

Prices Tor 67854 2000M02

Prices Van 64678 2000M01

Prices Van 84763 2013M10

;

run;

/* Represent missing values as blank */

options missing='';

proc transpose data=have out=want(drop=_name_);

    by category model notsorted;

    id date;

    var    unit;

run;

Ask a Question
Discussion stats
  • 2 replies
  • 190 views
  • 6 likes
  • 3 in conversation