How to lag a DAILY variable by six MONTHS and populate a new variable with that value?

[slee77]

I would greatly appreciate any assistance on this matter. I've been scouring the internet for solutions but my issue has multiple steps and sas is rather new to me. I am using SAS 9.4 TS Level 1M7.

My issue is that I need to populate a new numeric variable named 'ME6' with the 'ME_October_t' from the end of the month, six months ago for each 'seccode' which is an ID (I have many different IDs). I used INTNX to calculate that lagged date respective for each 'seccode' and titled it in my dataset, 'date6'. After that, I can't seem to find a solution. I hope someone with far better coding skills can assist me! I'm at my wits end!

It should look like the image below. I apologize for not using the actual data. If I did that, the picture would be too long since I have daily data and I need to lag it by six months. I hope you can understand it:

So from the last line of my sample dataset below which is for date 19770908, the 'ME6' would be 1425346000 which is the 'ME_October_t' value on 19770331.

Here's the sample dataset:

data WORK.HELP_ANALYSIS;
infile datalines dsd truncover;
input data_date:YYMMDDN. seccode:6. ME_October_t:32. date6:YYMMDDN8.;
format data_date YYMMDDN. seccode 6. date6 YYMMDDN8.;
datalines;
19770104 1301 14253460000 19760731
19770105 1301 14253460000 19760731
19770106 1301 14253460000 19760731
19770107 1301 14253460000 19760731
19770108 1301 14253460000 19760731
19770110 1301 14253460000 19760731
19770111 1301 14253460000 19760731
19770112 1301 14253460000 19760731
19770113 1301 14253460000 19760731
19770114 1301 14253460000 19760731
19770117 1301 14253460000 19760731
19770118 1301 14253460000 19760731
19770119 1301 14253460000 19760731
19770120 1301 14253460000 19760731
19770121 1301 14253460000 19760731
19770122 1301 14253460000 19760731
19770124 1301 14253460000 19760731
19770125 1301 14253460000 19760731
19770126 1301 14253460000 19760731
19770127 1301 14253460000 19760731
19770128 1301 14253460000 19760731
19770129 1301 14253460000 19760731
19770131 1301 14253460000 19760731
19770201 1301 14253460000 19760831
19770202 1301 14253460000 19760831
19770203 1301 14253460000 19760831
19770204 1301 14253460000 19760831
19770205 1301 14253460000 19760831
19770207 1301 14253460000 19760831
19770208 1301 14253460000 19760831
19770209 1301 14253460000 19760831
19770210 1301 14253460000 19760831
19770212 1301 14253460000 19760831
19770214 1301 14253460000 19760831
19770215 1301 14253460000 19760831
19770216 1301 14253460000 19760831
19770217 1301 14253460000 19760831
19770218 1301 14253460000 19760831
19770221 1301 14253460000 19760831
19770222 1301 14253460000 19760831
19770223 1301 14253460000 19760831
19770224 1301 14253460000 19760831
19770225 1301 14253460000 19760831
19770226 1301 14253460000 19760831
19770228 1301 14253460000 19760831
19770301 1301 14253460000 19760930
19770302 1301 14253460000 19760930
19770303 1301 14253460000 19760930
19770304 1301 14253460000 19760930
19770305 1301 14253460000 19760930
19770307 1301 14253460000 19760930
19770308 1301 14253460000 19760930
19770309 1301 14253460000 19760930
19770310 1301 14253460000 19760930
19770311 1301 14253460000 19760930
19770312 1301 14253460000 19760930
19770314 1301 14253460000 19760930
19770315 1301 14253460000 19760930
19770316 1301 14253460000 19760930
19770317 1301 14253460000 19760930
19770318 1301 14253460000 19760930
19770322 1301 14253460000 19760930
19770323 1301 14253460000 19760930
19770324 1301 14253460000 19760930
19770325 1301 14253460000 19760930
19770326 1301 14253460000 19760930
19770328 1301 14253460000 19760930
19770329 1301 14253460000 19760930
19770330 1301 14253460000 19760930
19770331 1301 14253460000 19760930
19770401 1301 14253460000 19761031
19770402 1301 14253460000 19761031
19770404 1301 14253460000 19761031
19770405 1301 14253460000 19761031
19770406 1301 14253460000 19761031
19770407 1301 14253460000 19761031
19770408 1301 14253460000 19761031
19770409 1301 14253460000 19761031
19770411 1301 14253460000 19761031
19770412 1301 14253460000 19761031
19770413 1301 14253460000 19761031
19770414 1301 14253460000 19761031
19770415 1301 14253460000 19761031
19770418 1301 14253460000 19761031
19770419 1301 14253460000 19761031
19770420 1301 14253460000 19761031
19770421 1301 14253460000 19761031
19770422 1301 14253460000 19761031
19770423 1301 14253460000 19761031
19770425 1301 14253460000 19761031
19770426 1301 14253460000 19761031
19770427 1301 14253460000 19761031
19770428 1301 14253460000 19761031
19770430 1301 14253460000 19761031
19770502 1301 14253460000 19761130
19770504 1301 14253460000 19761130
19770506 1301 14253460000 19761130
19770507 1301 14253460000 19761130
19770509 1301 14253460000 19761130
19770510 1301 14253460000 19761130
19770511 1301 14253460000 19761130
19770512 1301 14253460000 19761130
19770513 1301 14253460000 19761130
19770514 1301 14253460000 19761130
19770516 1301 14253460000 19761130
19770517 1301 14253460000 19761130
19770518 1301 14253460000 19761130
19770519 1301 14253460000 19761130
19770520 1301 14253460000 19761130
19770523 1301 14253460000 19761130
19770524 1301 14253460000 19761130
19770525 1301 14253460000 19761130
19770526 1301 14253460000 19761130
19770527 1301 14253460000 19761130
19770528 1301 14253460000 19761130
19770530 1301 14253460000 19761130
19770531 1301 14253460000 19761130
19770601 1301 14253460000 19761231
19770602 1301 14253460000 19761231
19770603 1301 14253460000 19761231
19770604 1301 14253460000 19761231
19770606 1301 14253460000 19761231
19770607 1301 14253460000 19761231
19770608 1301 14253460000 19761231
19770609 1301 14253460000 19761231
19770610 1301 14253460000 19761231
19770611 1301 14253460000 19761231
19770613 1301 14253460000 19761231
19770614 1301 14253460000 19761231
19770615 1301 14253460000 19761231
19770616 1301 14253460000 19761231
19770617 1301 14253460000 19761231
19770620 1301 14253460000 19761231
19770621 1301 14253460000 19761231
19770622 1301 14253460000 19761231
19770623 1301 14253460000 19761231
19770624 1301 14253460000 19761231
19770625 1301 14253460000 19761231
19770627 1301 14253460000 19761231
19770628 1301 14253460000 19761231
19770629 1301 14253460000 19761231
19770630 1301 14253460000 19761231
19770701 1301 14253460000 19770131
19770702 1301 14253460000 19770131
19770704 1301 14253460000 19770131
19770705 1301 14253460000 19770131
19770706 1301 14253460000 19770131
19770707 1301 14253460000 19770131
19770708 1301 14253460000 19770131
19770709 1301 14253460000 19770131
19770711 1301 14253460000 19770131
19770712 1301 14253460000 19770131
19770713 1301 14253460000 19770131
19770714 1301 14253460000 19770131
19770715 1301 14253460000 19770131
19770718 1301 14253460000 19770131
19770719 1301 14253460000 19770131
19770720 1301 14253460000 19770131
19770721 1301 14253460000 19770131
19770722 1301 14253460000 19770131
19770723 1301 14253460000 19770131
19770725 1301 14253460000 19770131
19770726 1301 14253460000 19770131
19770727 1301 14253460000 19770131
19770728 1301 14253460000 19770131
19770729 1301 14253460000 19770131
19770730 1301 14253460000 19770131
19770801 1301 14253460000 19770228
19770802 1301 14253460000 19770228
19770803 1301 14253460000 19770228
19770804 1301 14253460000 19770228
19770805 1301 14253460000 19770228
19770806 1301 14253460000 19770228
19770808 1301 14253460000 19770228
19770809 1301 14253460000 19770228
19770810 1301 14253460000 19770228
19770811 1301 14253460000 19770228
19770812 1301 14253460000 19770228
19770813 1301 14253460000 19770228
19770815 1301 14253460000 19770228
19770816 1301 14253460000 19770228
19770817 1301 14253460000 19770228
19770818 1301 14253460000 19770228
19770819 1301 14253460000 19770228
19770822 1301 14253460000 19770228
19770823 1301 14253460000 19770228
19770824 1301 14253460000 19770228
19770825 1301 14253460000 19770228
19770826 1301 14253460000 19770228
19770827 1301 14253460000 19770228
19770829 1301 14253460000 19770228
19770830 1301 14253460000 19770228
19770831 1301 14253460000 19770228
19770901 1301 14253460000 19770331
19770902 1301 14253460000 19770331
19770903 1301 14253460000 19770331
19770905 1301 14253460000 19770331
19770906 1301 14253460000 19770331
19770907 1301 14253460000 19770331
19770908 1301 14253460000 19770331
;;;;

[rudfaden]

I cannot read your test data

[AMSAS]

Hi @slee77 

I think this is what you are looking for. I simplified your original data and code.

Thanks

data work.have ;
	infile datalines ;
	input 
		data_date		:yymmdd8. /* YYMMDD8. */
		seccode			:$4. 
		ME_October_t	:32.		
	;
	put _all_ ;
	format 
		data_date	date7.
		;
datalines;
20200101 0001 1 
20200102 0001 2
20200131 0001 3
20200201 0001 4
20200202 0001 5
20200229 0001 6
20200101 0002 7
20200102 0002 8
20200131 0002 9
20200201 0002 10
20200202 0002 11
20200229 0002 12
20200701 0001 13
20200702 0001 14
20200731 0001 15
20200801 0001 16
20200802 0001 17
20200831 0001 18
20200701 0002 19
20200702 0002 20
20200731 0002 21
20200801 0002 22
20200802 0002 23
20200831 0002 24
;

proc sort 
	data	=work.have
	out		=work.srtdHave ;
	by
		seccode data_date ;
run ;

/* 
	Create a look up dataset containing just end of month data for each secode (work.lookup)
	Add matchDate variable to the master data (work.master). 
	This variable will be used to pull in the me6 value.
*/

data 
	/* Define the work.lookup SAS Dataset */
	work.lookup(
		rename=(
			ME_October_t=me6
			
		)
		keep=
			seccode 
			ME_October_t
			matchDate
						
	)
	work.master
	;
	format matchdate date7. ;
	set work.srtdhave ;
	/* If it's the last day of a month then output that record to the lookup table */
	if day(data_date+1)=1 then do ; /* is the current observation the last in a month? */
		matchDate=intnx("month",data_date,0) ; /* Set the matchDate to the start of the month */
		output work.lookup ;
	end ;
	/* Add a matchDate to the master dataset */
	matchdate=intnx('month',data_date,-6) ;
	output work.master ;
run ;

/* Merge the datasets bu seccode and matchdate if there is a record in the master dataset */
data work.want ;
	merge 
		work.master (in=master)
		work.lookup 
	;
	by 
		seccode matchdate ;
	if master ;
run ;

/**/

[slee77]

Hi @AMSAS,

Thanks so much for your input! I was about 80% successful because when I looked at the final product after I merge the 'lookup' and 'master' dataset, I had a few dates spread out throughout my dataset where there were missing values in the 'ME6' variable. I think I figured out why. My original dataset is only showing the days the stock market is open so that means no weekends or holidays. Therefore, some months begin after the 1st of the month or end before the actual end of the month and I think that messes up your code since it's looking for the day('data_date'+1)=1. It won't find that argument to be true in those cases and will skip that month.

If it's not too much trouble to ask, do you think you could modify the code to reflect this please? I am trying to work on a resolution myself as well. I'm wondering if maybe a bunch of OR arguments may work? For example: IF day('data_date'+1)=1 OR ('data_date'+1)=2 OR ('data_date'-1)=29? I'm grasping at straws right now but I will keep at it.

Thank you again for your time and generosity!

[AMSAS · Accepted solution]

Try the following

Things to note 

1. I assume you do not have observations for weekend days and holidays - This is a requirement of the source data for the sample code below to work correctly.
2. The sample data generated excludes weekend days (Sat/Sun), not holidays (as long as #1 is true the code should still work).
3. In your original code, SECCODE was defined as numeric. As it is a code I would recommend you define it as a character variable. 
4. The code now makes use of FIRST. and LAST. DATA Step Variables (see the documentation for details)
5. This assumes you only have a single year of data, see following post for code change to handle multiple years

/* Create Sample Data */
/* Data will be 7 days around the 1st of each month in 2023 not including Sat/Sun */
/* Note this does include holidays, I am assuming your source data does not have observations for weekends or holidays */

data work.have ;
	keep
		seccode 
		data_date 
		ME_October_t
	;
	format 
		data_date	date7.
		;
	ME_October_t=0 ;
	do seccode = "0001","0002" ;
		do temp_month=1 to 12 ;
			do data_date=mdy(temp_month,01,2023)-3 to mdy(temp_month,01,2023)+3 ;
				if weekday(data_date) not in (1,7) then do ;
					ME_October_t+1 ;	
					output ;	
				end ;
			end ;
		end ;
	end ;
run ;

/* Need to know what month the data_date is in */
data work.have2 ;
	set work.have ;
	mm=month(data_date) ;
	dow=putn(data_date,"downame3.") ;
run ;


proc sort 
	data	=work.have2
	out		=work.srtdHave ;
	by
		seccode mm data_date ;
run ;


/* 
	Create a look up dataset containing just end of month data for each secode (work.lookup)
	Add matchDate variable to the master data (work.master). 
	This variable will be used to pull in the me6 value.
*/

data 
	/* Define the work.lookup SAS Dataset */
	work.lookup(
		rename=(
			ME_October_t=me6
			
		)
		keep=
			seccode 
			ME_October_t
			matchDate
						
	)
	work.master
	;

	format matchdate date7. ;
	set work.srtdhave ;
	by
		seccode mm data_date ;
	/* If it's the last day of a month then output that record to the lookup table */
	if last.mm then do ; /* is the current observation the last in a month? */
		matchDate=intnx("month",data_date,0) ; /* Set the matchDate to the start of the month */
		output work.lookup ;
	end ;
	/* Add a matchDate to the master dataset */
	matchdate=intnx('month',data_date,-6) ;
	output work.master ;
run ;

proc sort 
	data	=work.lookup
	out		=work.srtdLookup ;
	by
		seccode matchdate ;
run ;

proc sort 
	data	=work.master 
	out		=work.srtdMaster ;
	by
		seccode matchdate ;
run ;


/* Merge the datasets by seccode and matchdate if there is a record in the master dataset */
data work.want ;
	merge 
		work.srtdMaster (in=master)
		work.srtdLookup 
	;
	by 
		seccode matchdate ;
	if master ;
run ;

/**/

[slee77]

Thank you so much @AMSAS ! Your code worked. I just had to do a small tweak to the following original code:

/* Need to know what month the data_date is in */
data work.have2 ;
	set work.have ;
	mm=month(data_date) ;
	dow=putn(data_date,"downame3.") ;
run ;

Since I have multiple years of data for each 'seccode', I had change the 'mm' variable to reflect both month and year:

/* Need to know what month the data_date is in */
data work.have2 ;
	set work.have ;
	mm=100*year(data_date)+ month(data_date);
	dow=putn(data_date,"downame3.") ;
run ;

That seems to work. I appreciate all your help with this!