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
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;
end;
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.)
MONTH_VAL = 1 will not always correspond to Feb 2018. It will be 1 based on whatever date is in the first set of records.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.