How to rename to rename multiple variables to one variable name

[hjjijkkl]

ID	COL1	CoL2	COL3	COL4
1		04/03/2020

I got a table like this after using proc transpose to convert horizontal variable to vertical. I wanted to know how to rename the variables COL1 COL2 COL3 COL4 to one variable name ''Date".

Thanks,

[mkeintz · Accepted solution]

You have a dataset with 4 variables, each containing a value.  You can't RENAME 4 variables to the same name.  You apparently want to do something else.   For instance, in the transposed data set, if you always have only one valid data value among the 4 variables, with the other 3 always missing, you could do something like this in a DATA step reading in the transposed data:

data new;
   set transposed;
   new_var=coalesce(co1,co2,col3,col4);
   format new_var date9.;
run;

The coalesce function takes the first non-missing value from the list of arguments.

[mklangley]

Or alternatively, you can transpose your data so that you have two columns: ID and Date, with a row for each date.

data have;
    input id col1 :mmddyy10. col2 :mmddyy10. col3 :mmddyy10. col4 :mmddyy10.;
    format col1-col4 mmddyy10.;
    datalines;
    1 . 04/03/2020 . .
    ;
run;

proc transpose data=have out=want (drop=_: rename=(col1 = date));
    by id;
run;

Result:

id	date
1	.
1	04/03/2020
1	.
1	.

It all depends on what you want, and whether or not there can be more than one date per ID, like @mkeintz mentioned.