- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 .