


****************************Trading Days Macro ***********************************;

/**********************************************************************************************/
/* FILENAME:        tddays.sas                                                    			  */
/* ORIGINAL AUTHOR: Eric Weisbrod						                                      */
/* MODIFIED BY:            													                  */
/* DATE CREATED:    Feb 22, 2021                                                              */
/* LAST MODIFIED:   Feb 22, 2021                                                              */
/* MACRO NAME:      tddays			                                                          */
/* ARGUMENTS:       1) DSETIN: input dataset containing an event date.					      */
/*                  2) DSETOUT: output dataset 									              */
/*                  3) BEGINWIN: first trading day relative to the event to collect 		  */
/*                  4) ENDWIN: last trading day relative to the event to collect              */
/*                  5) DATEVAR: variable name of the date in dsetin to use as the event date  */
/*                																			  */
/*                                        												      */
/* DESCRIPTION:     This macro uses a crsp trading date calendar to look up the event date and*/
/*					output a long (tidy) dataset of trading dates relative to the event date. */
/*					The output dataset can be used to then link returns, etc. The macro will  */
/*					give an error if the collection window includes dates that are beyond the */
/*					last available date of CRSP data available. 							  */
/* EXAMPLE(S):      1) %tddays(dsetin = mydata, dsetout = mydata2, datevar = rdq,			  */	
/*							beginwin=-1,endwin=1);									 	      */       
/*                      ==> Collect [-1,1] trading day windows around compustat quarterly 	  */
/*							earnings announcement date.	Helps to have permno already linked.  */
/**********************************************************************************************/

/* Code for how the trading date calendar is created*/

/*
proc sql;
create table dates as select distinct
date
from mycrsp.dsf
order by date;
quit;


data mycrsp.crspdates;
set dates;
n=_n_;
run;
*/



%macro tddays(dsetin=, dsetout=, datevar=, beginwin=0, endwin=0, calendarname=home.crspdates);

*enter a failsafe for out of range dates;
proc sql noprint;
select max(date)
  into :max_dt trimmed
  from &calendarname
;
quit;

data &dsetout (drop=rc n key);
format date YYMMDDN8.;
*instead of one hash we can do two hashes from the same crspdates set, 
one hash will be to match the eventdate, second hash will iterate through the event window;
declare hash nhash(dataset: "&calendarname", multidata:'no');
nhash.DefineKey("n");
nhash.DefineData("n","date");
nhash.DefineDone();
*see this hash is called datehash but uses same dataset;
declare hash datehash(dataset: "&calendarname", multidata:'no');
datehash.DefineKey("date");
datehash.DefineData("n","date");
datehash.DefineDone();

do until(eof);
	set &dsetin end = eof;
	format &datevar  YYMMDDN8.;
	key = 0;
	date = &datevar;
	td_days = %eval(&beginwin);
	rc=1;
	n_evtdate = .;
	n_days = .;

	*look up the event date;
	if not missing(&datevar) then do;
		do until(rc=0);
			rc=datehash.find();
			*if the date does not fall on a trading day,
			look at the next day, the loop will iterate until the date matches a trading day;
			if rc ^= 0 then do;
				date = INTNX("DAY",date,1);
				if date > &max_dt then do;
					put "Error: Date out of Range";
					stop;
				end;
			end;
			else do;
				n_evtdate = n;

			end;
		end;
	end;



	*now use the index of the event day in the trading calendar to find the begin and end days;
	n_days = ((n_evtdate+%eval(&endwin)) - (n_evtdate+%eval(&beginwin))) + 1;
	*if there is more than one day, output them all;
	if n_days > 1 then do;
		n = .;
		do key= (n_evtdate+%eval(&beginwin)) to (n_evtdate+%eval(&endwin)) by 1;

			n = .;
			n = key;
			date=.;
			rc=nhash.find();
			output;
			td_days +1;
		end;
	end;
	*If there is only one day, just look up that one;
	else do;
		key= (n_evtdate+%eval(&beginwin));
		n = key;
		date=.;
		rc=nhash.find();
		output;
	end;


end;
run;


%mend;

%put Trading Days Macro Loaded;



%macro tdmins(
dsetin=, 
dsetout=, 
datetimevar=, 
beginwin=0, 
endwin=0,
datelist=crspdates,
timelist=trade_minute_calendar);

	/*create a failsafe for out of range dates;*/
	proc sql noprint;
	select max(date)
	  into :max_dt trimmed
	  from &datelist
	;
	quit;


	/*this is similar to my daily trading days macro, but intraday; 
	*it takes in the ibes data with date and time timestamps, 
	*and finds the closest trading minute on the trading calendar;
	*all we are doing is finding the event time in trading time vs calendar time;*/
	data &dsetout (drop=rc n key);
	format date evtdate YYMMDDN8. min evttime  TIME8. evtdtm  datetime.;
	date = .;
	min = .;
	n = .;
		/*declare hash to check trading dates;
		*this loads the daily crsp trading calendar into RAM; */
		declare hash datehash(dataset: "fdpbox.crspdates", multidata:'no');
		datehash.DefineKey("date");
		datehash.DefineData("date");
		datehash.DefineDone();

		/*declare hash to check trading minutes;
		*it needs to be loaded twice, depending on the key I want to use; */
		declare hash timehash_n(dataset: "fdpbox.trade_minute_calendar", multidata:'no');
		timehash_n.DefineKey("n"); /*this is when I need to look up by min;*/
		timehash_n.DefineData("n","date","min");
		timehash_n.DefineDone();
		/*see this hash is called _d but uses same dataset; */
		declare hash timehash_d(dataset:  "fdpbox.trade_minute_calendar", multidata:'no');
		timehash_d.DefineKey("date","min"); /*this is when I want to look up by date and time;*/
		timehash_d.DefineData("date","min","n");
		timehash_d.DefineDone();

	do until(eof);
		set &dsetin end = eof;
		td_min = %eval(&beginwin);
		
		/*even if I initialize it as a missing value;*/
		key = 0;
		rc = 1;
		
		/*if it is the first observation in my dataset, load my hash/lookup tables into RAM;
		*A hash table is a lookup table to do matching, that is stored in RAM;
		*it is an advanced concept, you can google it; */


		/*set date to calendar event date and round minute DOWN to beginning of minute;*/
		date = datepart(&datetimevar);
		/*round  time to nearest minute;*/
		min = hms(hour(timepart(&datetimevar)),minute(timepart(&datetimevar)),0);

		/*add this datefind marker if I need to loop back here;*/
		datefind: rc=1;

		/*first find the right trading date; */
		do until(rc=0); /*rc is the returncode from the lookup table. It returns 0 for YES it DID find what it is looking for;*/
			rc=datehash.find(); /*whatever value is currently in the variable named "date" look for that in the datehash;*/
			/*if the date does not fall on a trading day, look at the next day, the loop will iterate until the date matches a trading day;*/
			if rc ^= 0 then do; /*if it didn't find the date;*/
				min = '04:00't; /*if not a trading day, and we have to go forward, start at 4AM next day;*/
				date = INTNX("DAY",date,1);
			end;
			if date > &max_dt then do;
				put "Error: Date out of Range";
				stop;
			end;
		end;

		/*now that we have a valid date, find a valid minute;*/
		rc=1;
		do until(rc=0);
			rc=timehash_d.find(); 
			/*if the time does not fall on a trading minute, go to next minute;*/
			if rc ^= 0 then do; /*if it didn't find that minute;*/
				min = INTNX("minute",min,1,'b');
			end;
			if min > '19:59't then do; /*if nothing then go to next day;*/
				min = '04:00't; /*if we have to go forward, start at 4AM next day;*/
				date = INTNX("DAY",date,1);
				go to datefind; /*go back up to the datefind to make sure the next day is a trading day and not a weekend;*/
			end;
		end;

		/*once we find the first valid trading minute after the revision, save all the info;*/
		evttime=min;
		evtdate=date;
		evtdtm = dhms(date,0,0,min);
		evtmin= n;

			
		/*now use the index to find the begin and end minutes; */
		/*if there is more than one minute, output them all; */
		if ((evtmin+%eval(&endwin)) - (evtmin+%eval(&beginwin))) > 0 then do;
			n = .;
			do key= (evtmin+%eval(&beginwin)) to (evtmin+%eval(&endwin)) by 1;

				n = .;
				n = key;
				date=.;
				min=.;
				rc=timehash_n.find();
				output;
				td_min +1;
			end;
		end;
		/*If there is only one minute, just look up that one; */		
		else do;
			key= (evtmin+%eval(&beginwin));
			n = key;
			date=.;
			min =.;
			rc=timehash_n.find();
			output;
		end;



	end;
	run;

%mend;


%put Trading Minutes Macro Loaded;



/******************************************************************************************/


**************************** Winsorize Macro ***********************************;

/**********************************************************************************************/
/* FILENAME:        Winsorize_Truncate.sas                                                    */
/* ORIGINAL AUTHOR: Steve Stubben (Stanford University)                                       */
/* MODIFIED BY:     Emmanuel De George and Atif Ellahie (LBS)			                                              */
/* DATE CREATED:    October 3, 2012                                                           */
/* LAST MODIFIED:   October 3, 2012                                                           */
/* MACRO NAME:      winsor			                                                          */
/* ARGUMENTS:       1) DSETIN: input dataset containing variables that will be win/trunc.     */
/*                  2) DSETOUT: output dataset (leave blank to overwrite DSETIN)              */
/*                  3) BYVAR: variable(s) used to form groups (leave blank for total sample)  */
/*                  4) VARS: variable(s) that will be winsorized/truncated                    */
/*                  5) TYPE: = W to winsorize and = T (or anything else) to truncate          */
/*                  6) PCTL = percentile points (in ascending order) to truncate/winsorize    */
/*                            values.  Default is 1st and 99th percentiles.                   */
/* DESCRIPTION:     This macro is capable of both truncating and winsorizing one or multiple  */
/*                  variables.  Truncated values are replaced with a missing observation      */
/*                  rather than deleting the observation.  This gives the user more control   */
/*                  over the resulting dataset.                                               */
/* EXAMPLE(S):      1) %winsor(dsetin = mydata, dsetout = mydata2, byvar = year,  			  */
/*                          vars = assets earnings, type = W, pctl = 0 98)                    */
/*                      ==> Winsorizes by year at 98% and puts resulting dataset into mydata2 */
/**********************************************************************************************/

%macro winsor	(dsetin = , 
			dsetout = , 
			byvar = none, 
			vars = , 
			type = W, 
			pctl = 1 99);
	%if &dsetout = %then
		%let dsetout = &dsetin;
	%let varL=;
	%let varH=;
	%let xn=1;

	%do %until (%scan(&vars,&xn)= );
		%let token = %scan(&vars,&xn);
		%let varL = &varL &token.L;
		%let varH = &varH &token.H;
		%let xn = %EVAL(&xn + 1);
	%end;

	%let xn = %eval(&xn-1);

	data xtemp;
		set &dsetin;
		%let dropvar =;

		%if &byvar = none %then
			%do;

	data xtemp;
		set xtemp;
		xbyvar = 1;
		%let byvar = xbyvar;
		%let dropvar = xbyvar;
			%end;

	proc sort data = xtemp;
		by &byvar;

		/*compute percentage cutoff values*/
	proc univariate data = xtemp noprint;
		by &byvar;
		var &vars;
		output out = xtemp_pctl PCTLPTS = &pctl PCTLPRE = &vars PCTLNAME = L H;

	data &dsetout;
		merge xtemp xtemp_pctl; /*merge percentage cutoff values into main dataset*/
		by &byvar;
		array trimvars{&xn} &vars;
		array trimvarl{&xn} &varL;
		array trimvarh{&xn} &varH;

		do xi = 1 to dim(trimvars);
			/*winsorize variables*/
			%if &type = W %then
				%do;
					if trimvars{xi} ne . then
						do;
							if (trimvars{xi} < trimvarl{xi}) then
								trimvars{xi} = trimvarl{xi};

							if (trimvars{xi} > trimvarh{xi}) then
								trimvars{xi} = trimvarh{xi};
						end;
				%end;

			/*truncate variables*/
			%else
				%do;
					if trimvars{xi} ne . then
						do;
							/*insert .T code if value is truncated*/
							if (trimvars{xi} < trimvarl{xi}) then
								trimvars{xi} = .T;

							if (trimvars{xi} > trimvarh{xi}) then
								trimvars{xi} = .T;
						end;
				%end;
		end;

		drop &varL &varH &dropvar xi;

		/*delete temporary datasets created during macro execution*/
	proc datasets library=work nolist;
		*delete xtemp xtemp_pctl;
	quit;

	run;

%mend winsor;


%put WINSOR macro loaded;



*# Fama French industry classification ;
* macro borrowed from https://github.com/JoostImpink/fama-french-industry/tree/master/SAS;
* Fama French 12 industry;
%macro ff12(dsin=, dsout=, sicvar=, varname=);

	data &dsout;
	set &dsin;
	
	/* industry assignments */
	if ( &sicvar ge 0100 and &sicvar le 0999) or ( &sicvar ge 2000 and &sicvar le 2399) or ( &sicvar ge 2700 and &sicvar le 2749) or ( &sicvar ge 2770 and &sicvar le 2799) or ( &sicvar ge 3100 and &sicvar le 3199) or ( &sicvar ge 3940 and &sicvar le 3989) then &varname= 1;
	if ( &sicvar ge 2500 and &sicvar le 2519) or ( &sicvar ge 2590 and &sicvar le 2599) or ( &sicvar ge 3630 and &sicvar le 3659) or ( &sicvar ge 3710 and &sicvar le 3711) or ( &sicvar ge 3714 and &sicvar le 3714) or ( &sicvar ge 3716 and &sicvar le 3716) or ( &sicvar ge 3750 and &sicvar le 3751) or ( &sicvar ge 3792 and &sicvar le 3792) or ( &sicvar ge 3900 and &sicvar le 3939) or ( &sicvar ge 3990 and &sicvar le 3999) then &varname= 2;
	if ( &sicvar ge 2520 and &sicvar le 2589) or ( &sicvar ge 2600 and &sicvar le 2699) or ( &sicvar ge 2750 and &sicvar le 2769) or ( &sicvar ge 3000 and &sicvar le 3099) or ( &sicvar ge 3200 and &sicvar le 3569) or ( &sicvar ge 3580 and &sicvar le 3629) or ( &sicvar ge 3700 and &sicvar le 3709) or ( &sicvar ge 3712 and &sicvar le 3713) or ( &sicvar ge 3715 and &sicvar le 3715) or ( &sicvar ge 3717 and &sicvar le 3749) or ( &sicvar ge 3752 and &sicvar le 3791) or ( &sicvar ge 3793 and &sicvar le 3799) or ( &sicvar ge 3830 and &sicvar le 3839) or ( &sicvar ge 3860 and &sicvar le 3899) then &varname= 3;
	if ( &sicvar ge 1200 and &sicvar le 1399) or ( &sicvar ge 2900 and &sicvar le 2999) then &varname= 4;
	if ( &sicvar ge 2800 and &sicvar le 2829) or ( &sicvar ge 2840 and &sicvar le 2899) then &varname= 5;
	if ( &sicvar ge 3570 and &sicvar le 3579) or ( &sicvar ge 3660 and &sicvar le 3692) or ( &sicvar ge 3694 and &sicvar le 3699) or ( &sicvar ge 3810 and &sicvar le 3829) or ( &sicvar ge 7370 and &sicvar le 7379) then &varname= 6;
	if ( &sicvar ge 4800 and &sicvar le 4899) then &varname= 7;
	if ( &sicvar ge 4900 and &sicvar le 4949) then &varname= 8;
	if ( &sicvar ge 5000 and &sicvar le 5999) or ( &sicvar ge 7200 and &sicvar le 7299) or ( &sicvar ge 7600 and &sicvar le 7699) then &varname= 9;
	if ( &sicvar ge 2830 and &sicvar le 2839) or ( &sicvar ge 3693 and &sicvar le 3693) or ( &sicvar ge 3840 and &sicvar le 3859) or ( &sicvar ge 8000 and &sicvar le 8099) then &varname=10;
	if ( &sicvar ge 6000 and &sicvar le 6999) then &varname=11;

	/*  Fama french siccodes file does not include industry codes for 'other'
		Set it to 12 (i.e. 'other') if not yet set
	 */
	if &varname eq . then &varname = 12;
	run;

%mend;

%put FF12 Macro Loaded;

* Fama French 49 industry;

%macro ff49(dsin=, dsout=, sicvar=, varname=);

	data &dsout;
	set &dsin;
	
	/* industry assignments */
	if ( &sicvar ge 0100 and &sicvar le 0199) or ( &sicvar ge 0200 and &sicvar le 0299) or ( &sicvar ge 0700 and &sicvar le 0799) or ( &sicvar ge 0910 and &sicvar le 0919) or ( &sicvar ge 2048 and &sicvar le 2048) then &varname= 1;
	if ( &sicvar ge 2000 and &sicvar le 2009) or ( &sicvar ge 2010 and &sicvar le 2019) or ( &sicvar ge 2020 and &sicvar le 2029) or ( &sicvar ge 2030 and &sicvar le 2039) or ( &sicvar ge 2040 and &sicvar le 2046) or ( &sicvar ge 2050 and &sicvar le 2059) or ( &sicvar ge 2060 and &sicvar le 2063) or ( &sicvar ge 2070 and &sicvar le 2079) or ( &sicvar ge 2090 and &sicvar le 2092) or ( &sicvar ge 2095 and &sicvar le 2095) or ( &sicvar ge 2098 and &sicvar le 2099) then &varname= 2;
	if ( &sicvar ge 2064 and &sicvar le 2068) or ( &sicvar ge 2086 and &sicvar le 2086) or ( &sicvar ge 2087 and &sicvar le 2087) or ( &sicvar ge 2096 and &sicvar le 2096) or ( &sicvar ge 2097 and &sicvar le 2097) then &varname= 3;
	if ( &sicvar ge 2080 and &sicvar le 2080) or ( &sicvar ge 2082 and &sicvar le 2082) or ( &sicvar ge 2083 and &sicvar le 2083) or ( &sicvar ge 2084 and &sicvar le 2084) or ( &sicvar ge 2085 and &sicvar le 2085) then &varname= 4;
	if ( &sicvar ge 2100 and &sicvar le 2199) then &varname= 5;
	if ( &sicvar ge 0920 and &sicvar le 0999) or ( &sicvar ge 3650 and &sicvar le 3651) or ( &sicvar ge 3652 and &sicvar le 3652) or ( &sicvar ge 3732 and &sicvar le 3732) or ( &sicvar ge 3930 and &sicvar le 3931) or ( &sicvar ge 3940 and &sicvar le 3949) then &varname= 6;
	if ( &sicvar ge 7800 and &sicvar le 7829) or ( &sicvar ge 7830 and &sicvar le 7833) or ( &sicvar ge 7840 and &sicvar le 7841) or ( &sicvar ge 7900 and &sicvar le 7900) or ( &sicvar ge 7910 and &sicvar le 7911) or ( &sicvar ge 7920 and &sicvar le 7929) or ( &sicvar ge 7930 and &sicvar le 7933) or ( &sicvar ge 7940 and &sicvar le 7949) or ( &sicvar ge 7980 and &sicvar le 7980) or ( &sicvar ge 7990 and &sicvar le 7999) then &varname= 7;
	if ( &sicvar ge 2700 and &sicvar le 2709) or ( &sicvar ge 2710 and &sicvar le 2719) or ( &sicvar ge 2720 and &sicvar le 2729) or ( &sicvar ge 2730 and &sicvar le 2739) or ( &sicvar ge 2740 and &sicvar le 2749) or ( &sicvar ge 2770 and &sicvar le 2771) or ( &sicvar ge 2780 and &sicvar le 2789) or ( &sicvar ge 2790 and &sicvar le 2799) then &varname= 8;
	if ( &sicvar ge 2047 and &sicvar le 2047) or ( &sicvar ge 2391 and &sicvar le 2392) or ( &sicvar ge 2510 and &sicvar le 2519) or ( &sicvar ge 2590 and &sicvar le 2599) or ( &sicvar ge 2840 and &sicvar le 2843) or ( &sicvar ge 2844 and &sicvar le 2844) or ( &sicvar ge 3160 and &sicvar le 3161) or ( &sicvar ge 3170 and &sicvar le 3171) or ( &sicvar ge 3172 and &sicvar le 3172) or ( &sicvar ge 3190 and &sicvar le 3199) or ( &sicvar ge 3229 and &sicvar le 3229) or ( &sicvar ge 3260 and &sicvar le 3260) or ( &sicvar ge 3262 and &sicvar le 3263) or ( &sicvar ge 3269 and &sicvar le 3269) or ( &sicvar ge 3230 and &sicvar le 3231) or ( &sicvar ge 3630 and &sicvar le 3639) or ( &sicvar ge 3750 and &sicvar le 3751) or ( &sicvar ge 3800 and &sicvar le 3800) or ( &sicvar ge 3860 and &sicvar le 3861) or ( &sicvar ge 3870 and &sicvar le 3873) or ( &sicvar ge 3910 and &sicvar le 3911) or ( &sicvar ge 3914 and &sicvar le 3914) or ( &sicvar ge 3915 and &sicvar le 3915) or ( &sicvar ge 3960 and &sicvar le 3962) or ( &sicvar ge 3991 and &sicvar le 3991) or ( &sicvar ge 3995 and &sicvar le 3995) then &varname= 9;
	if ( &sicvar ge 2300 and &sicvar le 2390) or ( &sicvar ge 3020 and &sicvar le 3021) or ( &sicvar ge 3100 and &sicvar le 3111) or ( &sicvar ge 3130 and &sicvar le 3131) or ( &sicvar ge 3140 and &sicvar le 3149) or ( &sicvar ge 3150 and &sicvar le 3151) or ( &sicvar ge 3963 and &sicvar le 3965) then &varname=10;
	if ( &sicvar ge 8000 and &sicvar le 8099) then &varname=11;
	if ( &sicvar ge 3693 and &sicvar le 3693) or ( &sicvar ge 3840 and &sicvar le 3849) or ( &sicvar ge 3850 and &sicvar le 3851) then &varname=12;
	if ( &sicvar ge 2830 and &sicvar le 2830) or ( &sicvar ge 2831 and &sicvar le 2831) or ( &sicvar ge 2833 and &sicvar le 2833) or ( &sicvar ge 2834 and &sicvar le 2834) or ( &sicvar ge 2835 and &sicvar le 2835) or ( &sicvar ge 2836 and &sicvar le 2836) then &varname=13;
	if ( &sicvar ge 2800 and &sicvar le 2809) or ( &sicvar ge 2810 and &sicvar le 2819) or ( &sicvar ge 2820 and &sicvar le 2829) or ( &sicvar ge 2850 and &sicvar le 2859) or ( &sicvar ge 2860 and &sicvar le 2869) or ( &sicvar ge 2870 and &sicvar le 2879) or ( &sicvar ge 2890 and &sicvar le 2899) then &varname=14;
	if ( &sicvar ge 3031 and &sicvar le 3031) or ( &sicvar ge 3041 and &sicvar le 3041) or ( &sicvar ge 3050 and &sicvar le 3053) or ( &sicvar ge 3060 and &sicvar le 3069) or ( &sicvar ge 3070 and &sicvar le 3079) or ( &sicvar ge 3080 and &sicvar le 3089) or ( &sicvar ge 3090 and &sicvar le 3099) then &varname=15;
	if ( &sicvar ge 2200 and &sicvar le 2269) or ( &sicvar ge 2270 and &sicvar le 2279) or ( &sicvar ge 2280 and &sicvar le 2284) or ( &sicvar ge 2290 and &sicvar le 2295) or ( &sicvar ge 2297 and &sicvar le 2297) or ( &sicvar ge 2298 and &sicvar le 2298) or ( &sicvar ge 2299 and &sicvar le 2299) or ( &sicvar ge 2393 and &sicvar le 2395) or ( &sicvar ge 2397 and &sicvar le 2399) then &varname=16;
	if ( &sicvar ge 0800 and &sicvar le 0899) or ( &sicvar ge 2400 and &sicvar le 2439) or ( &sicvar ge 2450 and &sicvar le 2459) or ( &sicvar ge 2490 and &sicvar le 2499) or ( &sicvar ge 2660 and &sicvar le 2661) or ( &sicvar ge 2950 and &sicvar le 2952) or ( &sicvar ge 3200 and &sicvar le 3200) or ( &sicvar ge 3210 and &sicvar le 3211) or ( &sicvar ge 3240 and &sicvar le 3241) or ( &sicvar ge 3250 and &sicvar le 3259) or ( &sicvar ge 3261 and &sicvar le 3261) or ( &sicvar ge 3264 and &sicvar le 3264) or ( &sicvar ge 3270 and &sicvar le 3275) or ( &sicvar ge 3280 and &sicvar le 3281) or ( &sicvar ge 3290 and &sicvar le 3293) or ( &sicvar ge 3295 and &sicvar le 3299) or ( &sicvar ge 3420 and &sicvar le 3429) or ( &sicvar ge 3430 and &sicvar le 3433) or ( &sicvar ge 3440 and &sicvar le 3441) or ( &sicvar ge 3442 and &sicvar le 3442) or ( &sicvar ge 3446 and &sicvar le 3446) or ( &sicvar ge 3448 and &sicvar le 3448) or ( &sicvar ge 3449 and &sicvar le 3449) or ( &sicvar ge 3450 and &sicvar le 3451) or ( &sicvar ge 3452 and &sicvar le 3452) or ( &sicvar ge 3490 and &sicvar le 3499) or ( &sicvar ge 3996 and &sicvar le 3996) then &varname=17;
	if ( &sicvar ge 1500 and &sicvar le 1511) or ( &sicvar ge 1520 and &sicvar le 1529) or ( &sicvar ge 1530 and &sicvar le 1539) or ( &sicvar ge 1540 and &sicvar le 1549) or ( &sicvar ge 1600 and &sicvar le 1699) or ( &sicvar ge 1700 and &sicvar le 1799) then &varname=18;
	if ( &sicvar ge 3300 and &sicvar le 3300) or ( &sicvar ge 3310 and &sicvar le 3317) or ( &sicvar ge 3320 and &sicvar le 3325) or ( &sicvar ge 3330 and &sicvar le 3339) or ( &sicvar ge 3340 and &sicvar le 3341) or ( &sicvar ge 3350 and &sicvar le 3357) or ( &sicvar ge 3360 and &sicvar le 3369) or ( &sicvar ge 3370 and &sicvar le 3379) or ( &sicvar ge 3390 and &sicvar le 3399) then &varname=19;
	if ( &sicvar ge 3400 and &sicvar le 3400) or ( &sicvar ge 3443 and &sicvar le 3443) or ( &sicvar ge 3444 and &sicvar le 3444) or ( &sicvar ge 3460 and &sicvar le 3469) or ( &sicvar ge 3470 and &sicvar le 3479) then &varname=20;
	if ( &sicvar ge 3510 and &sicvar le 3519) or ( &sicvar ge 3520 and &sicvar le 3529) or ( &sicvar ge 3530 and &sicvar le 3530) or ( &sicvar ge 3531 and &sicvar le 3531) or ( &sicvar ge 3532 and &sicvar le 3532) or ( &sicvar ge 3533 and &sicvar le 3533) or ( &sicvar ge 3534 and &sicvar le 3534) or ( &sicvar ge 3535 and &sicvar le 3535) or ( &sicvar ge 3536 and &sicvar le 3536) or ( &sicvar ge 3538 and &sicvar le 3538) or ( &sicvar ge 3540 and &sicvar le 3549) or ( &sicvar ge 3550 and &sicvar le 3559) or ( &sicvar ge 3560 and &sicvar le 3569) or ( &sicvar ge 3580 and &sicvar le 3580) or ( &sicvar ge 3581 and &sicvar le 3581) or ( &sicvar ge 3582 and &sicvar le 3582) or ( &sicvar ge 3585 and &sicvar le 3585) or ( &sicvar ge 3586 and &sicvar le 3586) or ( &sicvar ge 3589 and &sicvar le 3589) or ( &sicvar ge 3590 and &sicvar le 3599) then &varname=21;
	if ( &sicvar ge 3600 and &sicvar le 3600) or ( &sicvar ge 3610 and &sicvar le 3613) or ( &sicvar ge 3620 and &sicvar le 3621) or ( &sicvar ge 3623 and &sicvar le 3629) or ( &sicvar ge 3640 and &sicvar le 3644) or ( &sicvar ge 3645 and &sicvar le 3645) or ( &sicvar ge 3646 and &sicvar le 3646) or ( &sicvar ge 3648 and &sicvar le 3649) or ( &sicvar ge 3660 and &sicvar le 3660) or ( &sicvar ge 3690 and &sicvar le 3690) or ( &sicvar ge 3691 and &sicvar le 3692) or ( &sicvar ge 3699 and &sicvar le 3699) then &varname=22;
	if ( &sicvar ge 2296 and &sicvar le 2296) or ( &sicvar ge 2396 and &sicvar le 2396) or ( &sicvar ge 3010 and &sicvar le 3011) or ( &sicvar ge 3537 and &sicvar le 3537) or ( &sicvar ge 3647 and &sicvar le 3647) or ( &sicvar ge 3694 and &sicvar le 3694) or ( &sicvar ge 3700 and &sicvar le 3700) or ( &sicvar ge 3710 and &sicvar le 3710) or ( &sicvar ge 3711 and &sicvar le 3711) or ( &sicvar ge 3713 and &sicvar le 3713) or ( &sicvar ge 3714 and &sicvar le 3714) or ( &sicvar ge 3715 and &sicvar le 3715) or ( &sicvar ge 3716 and &sicvar le 3716) or ( &sicvar ge 3792 and &sicvar le 3792) or ( &sicvar ge 3790 and &sicvar le 3791) or ( &sicvar ge 3799 and &sicvar le 3799) then &varname=23;
	if ( &sicvar ge 3720 and &sicvar le 3720) or ( &sicvar ge 3721 and &sicvar le 3721) or ( &sicvar ge 3723 and &sicvar le 3724) or ( &sicvar ge 3725 and &sicvar le 3725) or ( &sicvar ge 3728 and &sicvar le 3729) then &varname=24;
	if ( &sicvar ge 3730 and &sicvar le 3731) or ( &sicvar ge 3740 and &sicvar le 3743) then &varname=25;
	if ( &sicvar ge 3760 and &sicvar le 3769) or ( &sicvar ge 3795 and &sicvar le 3795) or ( &sicvar ge 3480 and &sicvar le 3489) then &varname=26;
	if ( &sicvar ge 1040 and &sicvar le 1049) then &varname=27;
	if ( &sicvar ge 1000 and &sicvar le 1009) or ( &sicvar ge 1010 and &sicvar le 1019) or ( &sicvar ge 1020 and &sicvar le 1029) or ( &sicvar ge 1030 and &sicvar le 1039) or ( &sicvar ge 1050 and &sicvar le 1059) or ( &sicvar ge 1060 and &sicvar le 1069) or ( &sicvar ge 1070 and &sicvar le 1079) or ( &sicvar ge 1080 and &sicvar le 1089) or ( &sicvar ge 1090 and &sicvar le 1099) or ( &sicvar ge 1100 and &sicvar le 1119) or ( &sicvar ge 1400 and &sicvar le 1499) then &varname=28;
	if ( &sicvar ge 1200 and &sicvar le 1299) then &varname=29;
	if ( &sicvar ge 1300 and &sicvar le 1300) or ( &sicvar ge 1310 and &sicvar le 1319) or ( &sicvar ge 1320 and &sicvar le 1329) or ( &sicvar ge 1330 and &sicvar le 1339) or ( &sicvar ge 1370 and &sicvar le 1379) or ( &sicvar ge 1380 and &sicvar le 1380) or ( &sicvar ge 1381 and &sicvar le 1381) or ( &sicvar ge 1382 and &sicvar le 1382) or ( &sicvar ge 1389 and &sicvar le 1389) or ( &sicvar ge 2900 and &sicvar le 2912) or ( &sicvar ge 2990 and &sicvar le 2999) then &varname=30;
	if ( &sicvar ge 4900 and &sicvar le 4900) or ( &sicvar ge 4910 and &sicvar le 4911) or ( &sicvar ge 4920 and &sicvar le 4922) or ( &sicvar ge 4923 and &sicvar le 4923) or ( &sicvar ge 4924 and &sicvar le 4925) or ( &sicvar ge 4930 and &sicvar le 4931) or ( &sicvar ge 4932 and &sicvar le 4932) or ( &sicvar ge 4939 and &sicvar le 4939) or ( &sicvar ge 4940 and &sicvar le 4942) then &varname=31;
	if ( &sicvar ge 4800 and &sicvar le 4800) or ( &sicvar ge 4810 and &sicvar le 4813) or ( &sicvar ge 4820 and &sicvar le 4822) or ( &sicvar ge 4830 and &sicvar le 4839) or ( &sicvar ge 4840 and &sicvar le 4841) or ( &sicvar ge 4880 and &sicvar le 4889) or ( &sicvar ge 4890 and &sicvar le 4890) or ( &sicvar ge 4891 and &sicvar le 4891) or ( &sicvar ge 4892 and &sicvar le 4892) or ( &sicvar ge 4899 and &sicvar le 4899) then &varname=32;
	if ( &sicvar ge 7020 and &sicvar le 7021) or ( &sicvar ge 7030 and &sicvar le 7033) or ( &sicvar ge 7200 and &sicvar le 7200) or ( &sicvar ge 7210 and &sicvar le 7212) or ( &sicvar ge 7214 and &sicvar le 7214) or ( &sicvar ge 7215 and &sicvar le 7216) or ( &sicvar ge 7217 and &sicvar le 7217) or ( &sicvar ge 7219 and &sicvar le 7219) or ( &sicvar ge 7220 and &sicvar le 7221) or ( &sicvar ge 7230 and &sicvar le 7231) or ( &sicvar ge 7240 and &sicvar le 7241) or ( &sicvar ge 7250 and &sicvar le 7251) or ( &sicvar ge 7260 and &sicvar le 7269) or ( &sicvar ge 7270 and &sicvar le 7290) or ( &sicvar ge 7291 and &sicvar le 7291) or ( &sicvar ge 7292 and &sicvar le 7299) or ( &sicvar ge 7395 and &sicvar le 7395) or ( &sicvar ge 7500 and &sicvar le 7500) or ( &sicvar ge 7520 and &sicvar le 7529) or ( &sicvar ge 7530 and &sicvar le 7539) or ( &sicvar ge 7540 and &sicvar le 7549) or ( &sicvar ge 7600 and &sicvar le 7600) or ( &sicvar ge 7620 and &sicvar le 7620) or ( &sicvar ge 7622 and &sicvar le 7622) or ( &sicvar ge 7623 and &sicvar le 7623) or ( &sicvar ge 7629 and &sicvar le 7629) or ( &sicvar ge 7630 and &sicvar le 7631) or ( &sicvar ge 7640 and &sicvar le 7641) or ( &sicvar ge 7690 and &sicvar le 7699) or ( &sicvar ge 8100 and &sicvar le 8199) or ( &sicvar ge 8200 and &sicvar le 8299) or ( &sicvar ge 8300 and &sicvar le 8399) or ( &sicvar ge 8400 and &sicvar le 8499) or ( &sicvar ge 8600 and &sicvar le 8699) or ( &sicvar ge 8800 and &sicvar le 8899) or ( &sicvar ge 7510 and &sicvar le 7515) then &varname=33;
	if ( &sicvar ge 2750 and &sicvar le 2759) or ( &sicvar ge 3993 and &sicvar le 3993) or ( &sicvar ge 7218 and &sicvar le 7218) or ( &sicvar ge 7300 and &sicvar le 7300) or ( &sicvar ge 7310 and &sicvar le 7319) or ( &sicvar ge 7320 and &sicvar le 7329) or ( &sicvar ge 7330 and &sicvar le 7339) or ( &sicvar ge 7340 and &sicvar le 7342) or ( &sicvar ge 7349 and &sicvar le 7349) or ( &sicvar ge 7350 and &sicvar le 7351) or ( &sicvar ge 7352 and &sicvar le 7352) or ( &sicvar ge 7353 and &sicvar le 7353) or ( &sicvar ge 7359 and &sicvar le 7359) or ( &sicvar ge 7360 and &sicvar le 7369) or ( &sicvar ge 7374 and &sicvar le 7374) or ( &sicvar ge 7376 and &sicvar le 7376) or ( &sicvar ge 7377 and &sicvar le 7377) or ( &sicvar ge 7378 and &sicvar le 7378) or ( &sicvar ge 7379 and &sicvar le 7379) or ( &sicvar ge 7380 and &sicvar le 7380) or ( &sicvar ge 7381 and &sicvar le 7382) or ( &sicvar ge 7383 and &sicvar le 7383) or ( &sicvar ge 7384 and &sicvar le 7384) or ( &sicvar ge 7385 and &sicvar le 7385) or ( &sicvar ge 7389 and &sicvar le 7390) or ( &sicvar ge 7391 and &sicvar le 7391) or ( &sicvar ge 7392 and &sicvar le 7392) or ( &sicvar ge 7393 and &sicvar le 7393) or ( &sicvar ge 7394 and &sicvar le 7394) or ( &sicvar ge 7396 and &sicvar le 7396) or ( &sicvar ge 7397 and &sicvar le 7397) or ( &sicvar ge 7399 and &sicvar le 7399) or ( &sicvar ge 7519 and &sicvar le 7519) or ( &sicvar ge 8700 and &sicvar le 8700) or ( &sicvar ge 8710 and &sicvar le 8713) or ( &sicvar ge 8720 and &sicvar le 8721) or ( &sicvar ge 8730 and &sicvar le 8734) or ( &sicvar ge 8740 and &sicvar le 8748) or ( &sicvar ge 8900 and &sicvar le 8910) or ( &sicvar ge 8911 and &sicvar le 8911) or ( &sicvar ge 8920 and &sicvar le 8999) or ( &sicvar ge 4220 and &sicvar le 4229) then &varname=34;
	if ( &sicvar ge 3570 and &sicvar le 3579) or ( &sicvar ge 3680 and &sicvar le 3680) or ( &sicvar ge 3681 and &sicvar le 3681) or ( &sicvar ge 3682 and &sicvar le 3682) or ( &sicvar ge 3683 and &sicvar le 3683) or ( &sicvar ge 3684 and &sicvar le 3684) or ( &sicvar ge 3685 and &sicvar le 3685) or ( &sicvar ge 3686 and &sicvar le 3686) or ( &sicvar ge 3687 and &sicvar le 3687) or ( &sicvar ge 3688 and &sicvar le 3688) or ( &sicvar ge 3689 and &sicvar le 3689) or ( &sicvar ge 3695 and &sicvar le 3695) then &varname=35;
	if ( &sicvar ge 7370 and &sicvar le 7372) or ( &sicvar ge 7375 and &sicvar le 7375) or ( &sicvar ge 7373 and &sicvar le 7373) then &varname=36;
	if ( &sicvar ge 3622 and &sicvar le 3622) or ( &sicvar ge 3661 and &sicvar le 3661) or ( &sicvar ge 3662 and &sicvar le 3662) or ( &sicvar ge 3663 and &sicvar le 3663) or ( &sicvar ge 3664 and &sicvar le 3664) or ( &sicvar ge 3665 and &sicvar le 3665) or ( &sicvar ge 3666 and &sicvar le 3666) or ( &sicvar ge 3669 and &sicvar le 3669) or ( &sicvar ge 3670 and &sicvar le 3679) or ( &sicvar ge 3810 and &sicvar le 3810) or ( &sicvar ge 3812 and &sicvar le 3812) then &varname=37;
	if ( &sicvar ge 3811 and &sicvar le 3811) or ( &sicvar ge 3820 and &sicvar le 3820) or ( &sicvar ge 3821 and &sicvar le 3821) or ( &sicvar ge 3822 and &sicvar le 3822) or ( &sicvar ge 3823 and &sicvar le 3823) or ( &sicvar ge 3824 and &sicvar le 3824) or ( &sicvar ge 3825 and &sicvar le 3825) or ( &sicvar ge 3826 and &sicvar le 3826) or ( &sicvar ge 3827 and &sicvar le 3827) or ( &sicvar ge 3829 and &sicvar le 3829) or ( &sicvar ge 3830 and &sicvar le 3839) then &varname=38;
	if ( &sicvar ge 2520 and &sicvar le 2549) or ( &sicvar ge 2600 and &sicvar le 2639) or ( &sicvar ge 2670 and &sicvar le 2699) or ( &sicvar ge 2760 and &sicvar le 2761) or ( &sicvar ge 3950 and &sicvar le 3955) then &varname=39;
	if ( &sicvar ge 2440 and &sicvar le 2449) or ( &sicvar ge 2640 and &sicvar le 2659) or ( &sicvar ge 3220 and &sicvar le 3221) or ( &sicvar ge 3410 and &sicvar le 3412) then &varname=40;
	if ( &sicvar ge 4000 and &sicvar le 4013) or ( &sicvar ge 4040 and &sicvar le 4049) or ( &sicvar ge 4100 and &sicvar le 4100) or ( &sicvar ge 4110 and &sicvar le 4119) or ( &sicvar ge 4120 and &sicvar le 4121) or ( &sicvar ge 4130 and &sicvar le 4131) or ( &sicvar ge 4140 and &sicvar le 4142) or ( &sicvar ge 4150 and &sicvar le 4151) or ( &sicvar ge 4170 and &sicvar le 4173) or ( &sicvar ge 4190 and &sicvar le 4199) or ( &sicvar ge 4200 and &sicvar le 4200) or ( &sicvar ge 4210 and &sicvar le 4219) or ( &sicvar ge 4230 and &sicvar le 4231) or ( &sicvar ge 4240 and &sicvar le 4249) or ( &sicvar ge 4400 and &sicvar le 4499) or ( &sicvar ge 4500 and &sicvar le 4599) or ( &sicvar ge 4600 and &sicvar le 4699) or ( &sicvar ge 4700 and &sicvar le 4700) or ( &sicvar ge 4710 and &sicvar le 4712) or ( &sicvar ge 4720 and &sicvar le 4729) or ( &sicvar ge 4730 and &sicvar le 4739) or ( &sicvar ge 4740 and &sicvar le 4749) or ( &sicvar ge 4780 and &sicvar le 4780) or ( &sicvar ge 4782 and &sicvar le 4782) or ( &sicvar ge 4783 and &sicvar le 4783) or ( &sicvar ge 4784 and &sicvar le 4784) or ( &sicvar ge 4785 and &sicvar le 4785) or ( &sicvar ge 4789 and &sicvar le 4789) then &varname=41;
	if ( &sicvar ge 5000 and &sicvar le 5000) or ( &sicvar ge 5010 and &sicvar le 5015) or ( &sicvar ge 5020 and &sicvar le 5023) or ( &sicvar ge 5030 and &sicvar le 5039) or ( &sicvar ge 5040 and &sicvar le 5042) or ( &sicvar ge 5043 and &sicvar le 5043) or ( &sicvar ge 5044 and &sicvar le 5044) or ( &sicvar ge 5045 and &sicvar le 5045) or ( &sicvar ge 5046 and &sicvar le 5046) or ( &sicvar ge 5047 and &sicvar le 5047) or ( &sicvar ge 5048 and &sicvar le 5048) or ( &sicvar ge 5049 and &sicvar le 5049) or ( &sicvar ge 5050 and &sicvar le 5059) or ( &sicvar ge 5060 and &sicvar le 5060) or ( &sicvar ge 5063 and &sicvar le 5063) or ( &sicvar ge 5064 and &sicvar le 5064) or ( &sicvar ge 5065 and &sicvar le 5065) or ( &sicvar ge 5070 and &sicvar le 5078) or ( &sicvar ge 5080 and &sicvar le 5080) or ( &sicvar ge 5081 and &sicvar le 5081) or ( &sicvar ge 5082 and &sicvar le 5082) or ( &sicvar ge 5083 and &sicvar le 5083) or ( &sicvar ge 5084 and &sicvar le 5084) or ( &sicvar ge 5085 and &sicvar le 5085) or ( &sicvar ge 5086 and &sicvar le 5087) or ( &sicvar ge 5088 and &sicvar le 5088) or ( &sicvar ge 5090 and &sicvar le 5090) or ( &sicvar ge 5091 and &sicvar le 5092) or ( &sicvar ge 5093 and &sicvar le 5093) or ( &sicvar ge 5094 and &sicvar le 5094) or ( &sicvar ge 5099 and &sicvar le 5099) or ( &sicvar ge 5100 and &sicvar le 5100) or ( &sicvar ge 5110 and &sicvar le 5113) or ( &sicvar ge 5120 and &sicvar le 5122) or ( &sicvar ge 5130 and &sicvar le 5139) or ( &sicvar ge 5140 and &sicvar le 5149) or ( &sicvar ge 5150 and &sicvar le 5159) or ( &sicvar ge 5160 and &sicvar le 5169) or ( &sicvar ge 5170 and &sicvar le 5172) or ( &sicvar ge 5180 and &sicvar le 5182) or ( &sicvar ge 5190 and &sicvar le 5199) then &varname=42;
	if ( &sicvar ge 5200 and &sicvar le 5200) or ( &sicvar ge 5210 and &sicvar le 5219) or ( &sicvar ge 5220 and &sicvar le 5229) or ( &sicvar ge 5230 and &sicvar le 5231) or ( &sicvar ge 5250 and &sicvar le 5251) or ( &sicvar ge 5260 and &sicvar le 5261) or ( &sicvar ge 5270 and &sicvar le 5271) or ( &sicvar ge 5300 and &sicvar le 5300) or ( &sicvar ge 5310 and &sicvar le 5311) or ( &sicvar ge 5320 and &sicvar le 5320) or ( &sicvar ge 5330 and &sicvar le 5331) or ( &sicvar ge 5334 and &sicvar le 5334) or ( &sicvar ge 5340 and &sicvar le 5349) or ( &sicvar ge 5390 and &sicvar le 5399) or ( &sicvar ge 5400 and &sicvar le 5400) or ( &sicvar ge 5410 and &sicvar le 5411) or ( &sicvar ge 5412 and &sicvar le 5412) or ( &sicvar ge 5420 and &sicvar le 5429) or ( &sicvar ge 5430 and &sicvar le 5439) or ( &sicvar ge 5440 and &sicvar le 5449) or ( &sicvar ge 5450 and &sicvar le 5459) or ( &sicvar ge 5460 and &sicvar le 5469) or ( &sicvar ge 5490 and &sicvar le 5499) or ( &sicvar ge 5500 and &sicvar le 5500) or ( &sicvar ge 5510 and &sicvar le 5529) or ( &sicvar ge 5530 and &sicvar le 5539) or ( &sicvar ge 5540 and &sicvar le 5549) or ( &sicvar ge 5550 and &sicvar le 5559) or ( &sicvar ge 5560 and &sicvar le 5569) or ( &sicvar ge 5570 and &sicvar le 5579) or ( &sicvar ge 5590 and &sicvar le 5599) or ( &sicvar ge 5600 and &sicvar le 5699) or ( &sicvar ge 5700 and &sicvar le 5700) or ( &sicvar ge 5710 and &sicvar le 5719) or ( &sicvar ge 5720 and &sicvar le 5722) or ( &sicvar ge 5730 and &sicvar le 5733) or ( &sicvar ge 5734 and &sicvar le 5734) or ( &sicvar ge 5735 and &sicvar le 5735) or ( &sicvar ge 5736 and &sicvar le 5736) or ( &sicvar ge 5750 and &sicvar le 5799) or ( &sicvar ge 5900 and &sicvar le 5900) or ( &sicvar ge 5910 and &sicvar le 5912) or ( &sicvar ge 5920 and &sicvar le 5929) or ( &sicvar ge 5930 and &sicvar le 5932) or ( &sicvar ge 5940 and &sicvar le 5940) or ( &sicvar ge 5941 and &sicvar le 5941) or ( &sicvar ge 5942 and &sicvar le 5942) or ( &sicvar ge 5943 and &sicvar le 5943) or ( &sicvar ge 5944 and &sicvar le 5944) or ( &sicvar ge 5945 and &sicvar le 5945) or ( &sicvar ge 5946 and &sicvar le 5946) or ( &sicvar ge 5947 and &sicvar le 5947) or ( &sicvar ge 5948 and &sicvar le 5948) or ( &sicvar ge 5949 and &sicvar le 5949) or ( &sicvar ge 5950 and &sicvar le 5959) or ( &sicvar ge 5960 and &sicvar le 5969) or ( &sicvar ge 5970 and &sicvar le 5979) or ( &sicvar ge 5980 and &sicvar le 5989) or ( &sicvar ge 5990 and &sicvar le 5990) or ( &sicvar ge 5992 and &sicvar le 5992) or ( &sicvar ge 5993 and &sicvar le 5993) or ( &sicvar ge 5994 and &sicvar le 5994) or ( &sicvar ge 5995 and &sicvar le 5995) or ( &sicvar ge 5999 and &sicvar le 5999) then &varname=43;
	if ( &sicvar ge 5800 and &sicvar le 5819) or ( &sicvar ge 5820 and &sicvar le 5829) or ( &sicvar ge 5890 and &sicvar le 5899) or ( &sicvar ge 7000 and &sicvar le 7000) or ( &sicvar ge 7010 and &sicvar le 7019) or ( &sicvar ge 7040 and &sicvar le 7049) or ( &sicvar ge 7213 and &sicvar le 7213) then &varname=44;
	if ( &sicvar ge 6000 and &sicvar le 6000) or ( &sicvar ge 6010 and &sicvar le 6019) or ( &sicvar ge 6020 and &sicvar le 6020) or ( &sicvar ge 6021 and &sicvar le 6021) or ( &sicvar ge 6022 and &sicvar le 6022) or ( &sicvar ge 6023 and &sicvar le 6024) or ( &sicvar ge 6025 and &sicvar le 6025) or ( &sicvar ge 6026 and &sicvar le 6026) or ( &sicvar ge 6027 and &sicvar le 6027) or ( &sicvar ge 6028 and &sicvar le 6029) or ( &sicvar ge 6030 and &sicvar le 6036) or ( &sicvar ge 6040 and &sicvar le 6059) or ( &sicvar ge 6060 and &sicvar le 6062) or ( &sicvar ge 6080 and &sicvar le 6082) or ( &sicvar ge 6090 and &sicvar le 6099) or ( &sicvar ge 6100 and &sicvar le 6100) or ( &sicvar ge 6110 and &sicvar le 6111) or ( &sicvar ge 6112 and &sicvar le 6113) or ( &sicvar ge 6120 and &sicvar le 6129) or ( &sicvar ge 6130 and &sicvar le 6139) or ( &sicvar ge 6140 and &sicvar le 6149) or ( &sicvar ge 6150 and &sicvar le 6159) or ( &sicvar ge 6160 and &sicvar le 6169) or ( &sicvar ge 6170 and &sicvar le 6179) or ( &sicvar ge 6190 and &sicvar le 6199) then &varname=45;
	if ( &sicvar ge 6300 and &sicvar le 6300) or ( &sicvar ge 6310 and &sicvar le 6319) or ( &sicvar ge 6320 and &sicvar le 6329) or ( &sicvar ge 6330 and &sicvar le 6331) or ( &sicvar ge 6350 and &sicvar le 6351) or ( &sicvar ge 6360 and &sicvar le 6361) or ( &sicvar ge 6370 and &sicvar le 6379) or ( &sicvar ge 6390 and &sicvar le 6399) or ( &sicvar ge 6400 and &sicvar le 6411) then &varname=46;
	if ( &sicvar ge 6500 and &sicvar le 6500) or ( &sicvar ge 6510 and &sicvar le 6510) or ( &sicvar ge 6512 and &sicvar le 6512) or ( &sicvar ge 6513 and &sicvar le 6513) or ( &sicvar ge 6514 and &sicvar le 6514) or ( &sicvar ge 6515 and &sicvar le 6515) or ( &sicvar ge 6517 and &sicvar le 6519) or ( &sicvar ge 6520 and &sicvar le 6529) or ( &sicvar ge 6530 and &sicvar le 6531) or ( &sicvar ge 6532 and &sicvar le 6532) or ( &sicvar ge 6540 and &sicvar le 6541) or ( &sicvar ge 6550 and &sicvar le 6553) or ( &sicvar ge 6590 and &sicvar le 6599) or ( &sicvar ge 6610 and &sicvar le 6611) then &varname=47;
	if ( &sicvar ge 6200 and &sicvar le 6299) or ( &sicvar ge 6700 and &sicvar le 6700) or ( &sicvar ge 6710 and &sicvar le 6719) or ( &sicvar ge 6720 and &sicvar le 6722) or ( &sicvar ge 6723 and &sicvar le 6723) or ( &sicvar ge 6724 and &sicvar le 6724) or ( &sicvar ge 6725 and &sicvar le 6725) or ( &sicvar ge 6726 and &sicvar le 6726) or ( &sicvar ge 6730 and &sicvar le 6733) or ( &sicvar ge 6740 and &sicvar le 6779) or ( &sicvar ge 6790 and &sicvar le 6791) or ( &sicvar ge 6792 and &sicvar le 6792) or ( &sicvar ge 6793 and &sicvar le 6793) or ( &sicvar ge 6794 and &sicvar le 6794) or ( &sicvar ge 6795 and &sicvar le 6795) or ( &sicvar ge 6798 and &sicvar le 6798) or ( &sicvar ge 6799 and &sicvar le 6799) then &varname=48;
	if ( &sicvar ge 4950 and &sicvar le 4959) or ( &sicvar ge 4960 and &sicvar le 4961) or ( &sicvar ge 4970 and &sicvar le 4971) or ( &sicvar ge 4990 and &sicvar le 4991) then &varname=49;

	run;

%mend;


%put FF49 Macro Loaded;



/* ********************************************************************************* */
/* ******************** W R D S   R E S E A R C H   M A C R O S ******************** */
/* ********************************************************************************* */
/* WRDS Macro: ICLINK_CIZ                                                            */
/* Summary   : Create IBES-CRSP Link Table                                           */
/* Author    : Rabih Moussawi, WRDS                                                  */
/* Date      : September 25, 2006                                                    */ 
/* Update    : November 2024 by Freda Drechsler for CRSP CIZ data format             */
/* Variables : - IBESID and CRSPID are IBES and CRSP Names Datasets                  */
/*             - OUTSET: IBES-CRSP link table output dataset                         */
/* ********************************************************************************* */

%MACRO ICLINK_ciz (IBESID=IBES.ID,CRSPID=CRSP.STOCKNAMES_v2,OUTSET=WORK.ICLINK);
 
/* ********************************************************************************* */
/* FUNCTION: - Creates a link table between IBES TICKER and CRSP PERMNO              */
/*           - Scores links from 0 (best link) to 6                                  */
/* Possible IBES ID (names) file to use:                                             */
/*    Detail History: ID File                                                        */
/*    Summary History: IDSUM File                                                    */
/*    Recommendation Detail and Summary Statistics: RECDID and RECDIDSUM Files       */
/*                                                                                   */
/* INPUT: IBES and CRSP ID (or NAMES) Datasets, with historical identifiers list     */
/*       - IBES: IBES.ID, IBES.IDSUM, IBES.RECID, or IBES.RECIDSUM files             */
/*       - CRSP: CRSP.MSENAMES, CRSP.DSENAMES, or CRSP.STOCKNAMES files              */
/*                                                                                   */
/* OUTPUT: ICLINK set stored in prespecified directory                               */
/*       - SCORE variable: lower scores are better and high scores may need further  */
/*               checking before using them to link CRSP & IBES data.                */
/*               In computing the score, a CUSIP match is considered better than a   */
/*               TICKER match.  The score also includes a penalty for differences in */
/*               company names-- CNAME in IBES and COMNAM in CRSP. Name penalty is   */
/*               based upon SPEDIS, which is the spelling distance function in SAS.  */
/*               SPEDIS=0 is a perfect score and SPEDIS<30 is usually good           */
/*               enough to be considered a name match.                               */
/*               Note here that Exchange Ticker can also be used as Flag             */
/*          "SCORE" levels:                                                          */
/*               - 0: BEST match: using (cusip, cusip dates and company names)       */
/*                         or (exchange ticker, company names and 6-digit cusip)     */
/*               - 1: Cusips and cusip dates match but company names do not match    */
/*               - 2: Cusips and company names match but cusip dates do not match    */
/*               - 3: Cusips match but cusip dates and company names do not match    */
/*               - 4: tickers and 6-digit cusips match but comp names do not match   */
/*               - 5: tickers and names match but 6-digit cusips do not match        */
/*               - 6: tickers match but names and 6-digit cusips do not match        */
/* ********************************************************************************* */
 
options nonotes;
/* Check Validity of Library Assignments */
%if (%sysfunc(libref(crsp))) %then %do;
  %let cs=/wrds/crsp/sasdata/;
  libname crsp ("&cs/m_stock_v2","&cs/q_stock_v2","&cs/a_stock_v2");
%end;
%if (%sysfunc(libref(ibes))) %then %do; libname ibes "/wrds/ibes/sasdata"; %end;
 
/* Name End Dates variable in MSENAMES and DSENAMES is different than STOCKNAMES */
%if %sysfunc(upcase(&CRSPID)) ne CRSP.STOCKNAMES_V2 %then %let condition = %str(RENAME=(nameendt=nameenddt));
%else %let condition = ;

%put ;
%put ### START. Creating IBES-CRSP Link Table: ICLINK ;
%put ## IBES NAMES (ID) Dataset Used:  &IBESID;
%put ## CRSP NAMES (ID) Dataset Used:  &CRSPID;
%put ## Step1: Linking using CUSIPs... ;
 
/* Step 1: Link by CUSIP */
/* IBES: Get the list of IBES TICKERS for US firms in IBES */
proc sort data=&IBESID out=_IBES1 (keep=ticker cusip CNAME sdates);
  where USFIRM=1 and not(missing(cusip));
  by ticker cusip sdates;
run;
 
/* Create first and last 'start dates' for CUSIP link */
proc sql;
  create table _IBES2
  as select *, min(sdates) as fdate, max(sdates) as ldate
  from _IBES1
  group by ticker, cusip
  order by ticker, cusip, sdates;
quit;
 
/* Label date range variables and keep only most recent company name for CUSIP link */
data _IBES2;
  set _IBES2;
  by ticker cusip;
  if last.cusip;
  label fdate="First Start date of CUSIP record";
  label ldate="Last Start date of CUSIP record";
  format fdate ldate date9.;
  drop sdates;
run;
 
/* CRSP: Get all PERMNO-NCUSIP combinations */
proc sort data=&CRSPID out=_CRSP1 (keep=PERMNO CUSIP IssuerNm name: &condition);
  where not missing(CUSIP);
  by PERMNO CUSIP namedt;
run;
 
/* Arrange effective dates for CUSIP link */
proc sql;
  create table _CRSP2
  as select PERMNO,CUSIP,IssuerNm,min(namedt)as namedt,max(nameenddt) as nameenddt
  from _CRSP1
  group by PERMNO, CUSIP
  order by PERMNO, CUSIP, NAMEDT;
quit;
 
/* Label date range variables and keep only most recent company name */
data _CRSP2;
  set _CRSP2;
  by permno cusip;
  if last.cusip;
  label namedt="Start date of CUSIP record";
  label nameenddt="End date of CUSIP record";
  format namedt nameenddt date9.;
run;
 
/* Create CUSIP Link Table */
/* CUSIP date ranges are only used in scoring as CUSIPs are not reused for
    different companies overtime */
proc sql;
  create table _LINK1_1
  as select *
  from _IBES2 as a, _CRSP2 as b
  where a.CUSIP = b.CUSIP
  order by TICKER, PERMNO, ldate;
quit;
 
/* Score links using CUSIP date range and company name spelling distance */
/* Idea: date ranges the same cusip was used in CRSP and IBES should intersect */
data _LINK1_2;
  set _LINK1_1;
  by TICKER PERMNO;
  if last.permno; /* Keep link with most recent company name */
  name_dist = min(spedis(cname,IssuerNm),spedis(IssuerNm,cname));
  if (not ((ldate < namedt) or (fdate > nameenddt))) and name_dist < 30 then SCORE = 0;
    else if (not ((ldate < namedt) or (fdate > nameenddt))) then score = 1;
        else if name_dist < 30 then SCORE = 2;
      else SCORE = 3;
  keep TICKER PERMNO cname IssuerNm score;
run;
 
%put ## Step2: Linking using TICKERs... ;
/* Step 2: Find links for the remaining unmatched cases using Exchange Ticker */
/* Identify remaining unmatched cases */
proc sql;
  create table _NOMATCH1
  as select distinct a.*
  from _IBES1 (keep=ticker) as a
  where a.ticker NOT in (select ticker from _LINK1_2)
  order by a.ticker;
quit;
 
/* Drop Step1 Tables*/
proc sql; drop table _IBES1,_IBES2,_CRSP1,_CRSP2; quit;
 
/* Add IBES identifying information */
proc sql;
  create table _NOMATCH2
  as select b.ticker, b.CNAME, b.OFTIC, b.sdates, b.cusip
  from _NOMATCH1 as a, &IBESID as b
  where a.ticker = b.ticker and not (missing(b.OFTIC))
  order by ticker, oftic, sdates;
quit; 
 
/* Create first and last 'start dates' for Exchange Tickers */
proc sql;
  create table _NOMATCH3
  as select *, min(sdates) as fdate, max(sdates) as ldate
  from _NOMATCH2
  group by ticker, oftic
  order by ticker, oftic, sdates;
quit;
 
/* Label date range variables and keep only most recent company name */
data _NOMATCH3;
  set _NOMATCH3;
  by ticker oftic;
  if last.oftic;
  label fdate="First Start date of OFTIC record";
  label ldate="Last Start date of OFTIC record";
  format fdate ldate date9.;
  drop sdates;
run;
 
/* Get entire list of CRSP stocks with Exchange Ticker information */
proc sort data=&CRSPID out=_CRSP1 (keep=ticker IssuerNm permno cusip name: &condition);
  where not missing(ticker);
  by permno ticker namedt;
run;
 
/* Arrange effective dates for link by Exchange Ticker */
proc sql;
  create table _CRSP2
  as select permno,IssuerNm,ticker as crsp_ticker,cusip,
              min(namedt)as namedt,max(nameenddt) as nameenddt
  from _CRSP1
  group by permno, ticker
  order by permno, crsp_ticker, namedt;
quit;
/* CRSP exchange ticker renamed to crsp_ticker to avoid confusion with IBES TICKER */
 
/* Label date range variables and keep only most recent company name */
data _CRSP2;
  set _CRSP2;
  by permno crsp_ticker;
  if  last.crsp_ticker;
  label namedt="Start date of exch. ticker record";
  label nameenddt="End date of exch. ticker record";
  format namedt nameenddt date9.;
run;
 
/* Merge remaining unmatched cases using Exchange Ticker */
/* Note: Use ticker date ranges as exchange tickers are reused overtime */
proc sql;
  create table _LINK2_1
  as select a.ticker,a.oftic, b.permno, a.cname, b.IssuerNm, a.cusip, b.cusip as crsp_cusip, a.ldate
  from _NOMATCH3 as a, _CRSP2 as b
  where a.oftic = b.crsp_ticker and
     (ldate>=namedt) and (fdate<=nameenddt)
  order by ticker, oftic, ldate;
quit;
 
/* Score using company name using 6-digit CUSIP and company name spelling distance */
data _LINK2_2;
  set _LINK2_1;
  name_dist = min(spedis(cname,IssuerNm),spedis(IssuerNm,cname));
  if substr(cusip,1,6)=substr(crsp_cusip,1,6) and name_dist < 30 then SCORE=0;
  else if substr(cusip,1,6)=substr(crsp_cusip,1,6) then score = 4;
  else if name_dist < 30 then SCORE = 5;
      else SCORE = 6;
run;
 
/* Some companies may have more than one TICKER-PERMNO link,         */
/* so re-sort and keep the case (PERMNO & Company name from CRSP)    */
/* that gives the lowest score for each IBES TICKER (first.ticker=1) */
proc sort data=_LINK2_2; by ticker score; run;
data _LINK2_3;
  set _LINK2_2;
  by ticker score;
  if first.ticker;
  keep ticker permno cname IssuerNm permno score;
run;
 
%put ## Step3: Finalizing Links and Scores... ;
/* Step 3: Add Exchange Ticker links to CUSIP links      */
/* Create Labels for ICLINK dataset and variables        */
/* Create final link table and save it in prespecified directory */
data &OUTSET (label="IBES-CRSP Link Table");
  set _LINK1_2 _LINK2_3;
label CNAME = "Company Name in IBES";
label IssuerNm= "Company Name in CRSP";
label SCORE= "Link Score: 0(best) - 6";
run;
 
/* Final Sort */
proc sort data=&OUTSET; by TICKER SCORE PERMNO; run;
 
%put ## Step4: Link Table &OUTSET Ready... ;
/* House Cleaning */
proc sql;
drop table _CRSP1,_CRSP2,
           _LINK1_1,_LINK1_2,_LINK2_1,_LINK2_2,_LINK2_3,
           _NOMATCH1,_NOMATCH2,_NOMATCH3;
quit;
%put ### DONE . ; %put ;
options notes;
%MEND ICLINK_ciz;
 
/* ********************************************************************************* */
/* *************  Material Copyright Wharton Research Data Services  *************** */
/* ****************************** All Rights Reserved ****************************** */
/* ********************************************************************************* */


%put ICLINK Macro Loaded;









%put GOOD JOB! YOU LOADED THE MACROS;

/******************************************************************************************/


****************************Load .env File Macro **********************************;

/**********************************************************************************************/
/* MACRO NAME:      load_env                                                                  */
/* DESCRIPTION:     Reads a .env file and sets each KEY=VALUE pair as a global SAS macro      */
/*                  variable. Default file location is the repo root, computed by stripping   */
/*                  the .sas filename and then the src/ directory from SAS_EXECFILEPATH.      */
/*                  Pass file= for an override.                                                */
/* USAGE:           %load_env;                                                                 */
/*                  %load_env(file=D:/some/other/.env);                                        */
/* NOTE:            SAS has no native dotenv library; this is a small parser for simple       */
/*                  KEY=VALUE lines. Lines starting with # are skipped.                       */
/**********************************************************************************************/

%macro load_env(file=);
    %local script_path script_dir parent_dir slash_pos;
    %if %length(&file) = 0 %then %do;
        /* Two ways SAS exposes the running script's path:
            - getoption(sysin) is set when invoked as `sas -SYSIN file`
              (batch mode, e.g. via batch_run_sas)
            - sysget(SAS_EXECFILEPATH) is set by Enhanced Editor and
              Enterprise Guide on interactive Submit
           Try SYSIN first and only fall back to sysget when empty, since
           sysget on an undefined env var prints a noisy WARNING. */
        %let script_path = %sysfunc(getoption(sysin));
        %if %length(&script_path) = 0 %then %do;
            %let script_path = %sysfunc(sysget(SAS_EXECFILEPATH));
        %end;
        %if %length(&script_path) = 0 %then %do;
            %put ERROR: load_env: cannot determine script path (both;
            %put ERROR: SYSIN option and SAS_EXECFILEPATH are empty).;
            %put ERROR: pass an explicit path via %nrstr(%load_env(file=...));
            %return;
        %end;
        * Strip the .sas filename to get src/ ;
        %let slash_pos = %sysfunc(findc(&script_path, \, b));
        %let script_dir = %substr(&script_path, 1, %eval(&slash_pos - 1));
        * Strip the last directory (typically src) to get the repo root ;
        %let slash_pos = %sysfunc(findc(&script_dir, \, b));
        %let parent_dir = %substr(&script_dir, 1, %eval(&slash_pos - 1));
        %let file = &parent_dir\.env;
    %end;
    %put NOTE: load_env reading &file;
    %if not %sysfunc(fileexist(&file)) %then %do;
        %put ERROR: load_env: file does not exist at &file;
        %put ERROR: pass an explicit path via %nrstr(%load_env(file=...));
        %return;
    %end;
    * Read the .env file via a data step. Each KEY=VALUE line becomes a global ;
    * macro variable set by CALL SYMPUTX("...", ..., "G").                       ;
    filename _envf "&file";
    data _null_;
        infile _envf truncover;
        input line $4096.;
        line = strip(line);
        if length(line) = 0 then return;
        if substr(line, 1, 1) = "#" then return;
        eqpos = index(line, "=");
        if eqpos <= 1 then return;
        key = strip(substr(line, 1, eqpos - 1));
        if eqpos < length(line) then val = strip(substr(line, eqpos + 1));
        else val = "";
        call symputx(key, val, "G");
    run;
    filename _envf clear;
%mend;
