Read Excel file with dates are as veriable name

[Nikhiljain22740]

Hi,

I need help to read an Excel file that have Emp_id, Joining_date and columns 01/04/2016, 01/05/2016, 01/06/2016.... till 01/07/2018. Please find data given below.

Emp_id Joining_date 01/04/2016 01/05/2016 01/06/2016..
101 05/04/2016 .15 .40 .28
102 21/04/2016 .56 .34 .67
103 13/05/2016 .39 .61 .84
.....


Now import that Excel file into SAS and want data for next 3 months from date of joining. So like first observation date of joining is April 2016 then we need the data for this employee from April 2016 to June 2016. Similar for all employees.

Desired output :

Emp_id Joining_date M1 M2 M3
101 05/04/2016 .15 .40 .28
102 21/04/2016 .56 .34 .67
103 13/05/2016 .84
.......

Thanks
Nikhil Jain

[Nikhiljain22740]

I think you didn't understand my question, first prepare an Excel file with the data given and import it into SAS then try to find out the solution.

Actually if I'm importing such file it's automatically taking some different variable name like F3, F4...

Or give me your mail id will send data file, so it'll be easy for you to find solution.

[Tom]

It is not a good idea to put data into metadata.  Any way you can have the Excel file transposed BEFORE you import it so the dates are in a column instead of being used as column headers?

There are ways to deal with trying to read the dates as data using PROC IMPORT by adding the GETNAMES=NO optional statement.  Read the dates line as one dataset and the body of the data as another.

But your real question is how to use the date value you do have as a variable so that you can find the values that correspond to the follow-up months.

So let's assume you have this data set.

data have ;
 input Emp_id Joining_date _2016_04_01 _2016_05_01 _2016_06_01 ;
 informat joining_date yymmdd. ;
 format joining_date yymmdd10. ;
cards;
101 2016-04-05 0.15 0.40 0.28
102 2016-04-21 0.56 0.34 0.67
103 2016-05-13 0.39 0.61 0.84
;;;;

Note: Don't use DMY (or MDY) order to display dates. Either way you will confuse half of your audience.

So now to make new variables that have the values relative to Joining_date you just need calculate the indexes into the two arrays.

data want ;
  set have ;
  array absolute _201: ;
  array relative (3) ;
  do from=intck('month','01APR2016'd,joining_date)+1 to dim(absolute);
    to=sum(to,1);
    relative(to) = absolute(from);
  end;
  drop from to;
run;

                   Joining_    _2016_    _2016_    _2016_
Obs    Emp_id          date     04_01     05_01     06_01    relative1    relative2    relative3

 1       101     2016-04-05     0.15      0.40      0.28        0.15         0.40         0.28
 2       102     2016-04-21     0.56      0.34      0.67        0.56         0.34         0.67
 3       103     2016-05-13     0.39      0.61      0.84        0.61         0.84          .