topic Re: Reading a date field and creating a MONTH variable. in SAS Programming

Reading a date field and creating a MONTH variable.

tapas_16880 — Tue, 06 Mar 2018 18:30:01 GMT

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

Re: Reading a date field and creating a MONTH variable.

Astounding — Tue, 06 Mar 2018 18:28:26 GMT

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.)

Re: Reading a date field and creating a MONTH variable.

tapas_16880 — Tue, 06 Mar 2018 18:33:24 GMT

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.

Re: Reading a date field and creating a MONTH variable.

mkeintz — Tue, 06 Mar 2018 20:10:38 GMT

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;