topic Re: transpose dates into COL and keep format in SAS Procedures

transpose dates into COL and keep format

podarum — Tue, 17 Dec 2013 15:38:41 GMT

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

Re: transpose dates into COL and keep format

art297 — Tue, 17 Dec 2013 15:49:44 GMT

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.

Re: transpose dates into COL and keep format

AhmedAl_Attar — Tue, 17 Dec 2013 16:16:15 GMT

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;