Reshaping dataset with dates as column names to panel

sworobec1 — Thu, 24 Sep 2020 11:57:12 GMT

Hello All,

I have a dataset that has column names as dates and I'd like to convert it to a panel structure where those dates are all under one column called "date". I've attached what the data looks like and an desired version of the data. The actual data has a few years worth of months as column names. The 1 and 0s denote whether the car part was working or not. I don't think a simple proc transpose will solve this issue and I don't know where to start. Would appreciate your help.

Thank you.

Re: Reshaping dataset with dates as column names to panel

andreas_lds — Thu, 24 Sep 2020 12:48:02 GMT

Congratulation: you have data in one of the file-formats hardly usable for anything, but data-presentation and to make the problem even more interesting the columns are named in an ill-way. The following code may need some tweaks, but it should be a useful starting point:

/* automatically rename variables to be valid names */
options validvarname=v7;
/* keep-Option to prevent reading columns that have no content,
 * with _: all date-columns stay in the dataset */
proc import file="testdata.xlsx" out=work.have(keep=Car_Id Part _:) replace dbms=xlsx;
run;

/* reshaping */
proc transpose data=work.have out=work.transposed(drop=_label_ rename=(col1=Working)) name=excel_date;
   by Car_Id Part;
   var _:;
run;

/* fixing the date-variable */
data work.want;
   set work.transposed;
   
   length Date 8;
   format Date mmddyys10.;
   
   /* see https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/068-29.pdf */
   Date = input(substr(excel_date, 2) - 21916, best.);
   
   drop excel_date;
run;

topic Reshaping dataset with dates as column names to panel in SAS Programming

Reshaping dataset with dates as column names to panel

Re: Reshaping dataset with dates as column names to panel