BookmarkSubscribeRSS Feed
tapas_16880
Calcite | Level 5

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

3 REPLIES 3
Astounding
PROC Star

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

tapas_16880
Calcite | Level 5

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.

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 856 views
  • 1 like
  • 3 in conversation