topic How to handle different date formats when I transpose a table? in SAS Programming

How to handle different date formats when I transpose a table?

BarryP — Fri, 18 Jan 2019 21:33:47 GMT

I want to create a table that displays different examples of date formats.

Data Date_formats;

var1 = 20165;

var2 = 20165;

var3 = 20165;

format var1 date7. var2 date9. var3 date11.;

run;

The output is a table that looks like this:

var1 var2 var3
18MAR15 18MAR2015 18-MAR-2015

I want to transpose this table to look like the following:
var1 18MAR15
var2 18MAR2015
var3 18-MAR-2015

But instead when I use Proc Transpose, it just turns all of the dates back to the SAS date format (20165).

I tried converting them all to characters using var1=put(20165,date7.), but then proc transpose wont't display them at all.

Thanks in advance!

-Barry

Re: How to handle different date formats when I transpose a table?

ballardw — Fri, 18 Jan 2019 22:01:27 GMT

A single variable (column) can only have one format. If you want a data set to get something that looks like that you would have to create new character variables from var1 - var3 using the PUT function with the desired format and then transpose the text variables .

We would have to see your data and complete code to determine why the character version you tried didn't produce desired results.

var1=put(20165,date7.),

would not create a character variable because you have already defined var1 as numeric. You would need to do something like.

var1_c = put(var1,date7.) ;

How do you expect to use that transposed data set? Perhaps you are transposing when it is not needed.

Re: How to handle different date formats when I transpose a table?

Cynthia_sas — Fri, 18 Jan 2019 22:10:25 GMT

Hi:
In your transposed data, var1, var2 and var3 will each have the value of 20165 (march 18, 2015). However, in a data set, you can only have 1 format associated with a single column so inside the data, the variable value. In a report, on the other hand, you could display each row's value with a different format. Other folks can answer the transpose question. If you have 2 variables in the transposed data called TYPE and VAL, then this would work in PROC REPORT. As shown here:

Cynthia

Re: How to handle different date formats when I transpose a table?

Tom — Sat, 19 Jan 2019 03:02:14 GMT

I tried converting them all to characters using var1=put(20165,date7.), but then proc transpose wont't display them at all.

PROC TRANSPOSE can work with character variables, but you need list them in the VAR statement. If you leave off the VAR statement then PROC TRANSPOSE will just transpose the numeric variables.

Re: How to handle different date formats when I transpose a table?

BarryP — Sat, 19 Jan 2019 03:35:49 GMT

Thanks, Tom. This was the step that I was misunderstanding the most. One I convert to characters, this seems to solve my problem:

proc transpose data=Date_Formats;

var var1 var2 var3;

run;