BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

2 REPLIES 2
art297
Opal | Level 21

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.

AhmedAl_Attar
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 1314 views
  • 6 likes
  • 3 in conversation