03-06-2018 01:08 PM - edited 03-06-2018 01:30 PM
Hi All, Need a help. I have a flat where on 25th position, I have a date value of DDMMYY6. (or can be read as character as well). Now, the data is stacked on a daily basis in this file. So, first 100 records will have lets say 160218 (16th Feb 2018 i.e. DAY 1) populated in 25th column onwards for 6 positions. From 101th records, DAY 2 starts i.e. column 25 to column 30 will have 150218, so on and so forth. When we will go to the last record, we will have lets say 270817.
I need to write a code which will read this date values and will create a MONTH_VAL variables regardless of what is the starting date in record 1 and what is the final date in this file. So, it will read month and year value and assign month_val accordingly.
The data is will have this date value in descending order i.e. highest dates will appear in the first set of records.
So, my code will read all other values based on different offset positions in the file plus:
MONTH_VAL will be 1 for 160218
MONTH_VAL will be 1 for 150218
MONTH_VAL will be 1 for 140218
MONTH_VAL will be 2 for 310118
MONTH_VAL will be 7 for 270817
this file removes weekends/bank holidays anyways, so that is not a concern.
Please can someone share the logic how can I derive this MONTH_VAL
MONTH_VAL will be lets say 270817
03-06-2018 01:28 PM
You do need to learn about how SAS handles dates. However, that can be postponed here. Your question can be answered based on the months and years in your existing data while ignoring the actual date.
When you read in the data, part of your INPUT statement can be:
input m 27-28 y 29-30;
Then you can compute MONTH_VAL based on M and Y. For example:
if _n_=1 then do;
first_m = m;
first_y = y;
retain first_m first_y;
month_val = (1 + first_m - m) + 12 * (first_y - y);
This assumes that you want MONTH_VAL=1 for whatever date is first in your data. (If you want MONTH_VAL=1 to always correspond to February 2018, the code becomes much simpler.)
03-06-2018 03:10 PM
You can use the INTCK function to count the months between two dates. So all you need is the date of the first record (retained across all observations) and date of the N'th record:
data want (drop=_:); input @25 date ddmmyy6.; format date date9.; if _n_=1 then _first_date=date; retain _first_date; month=1+intck('month',date,_first_date); run;