topic Re: Adding a time macro variable in SAS Programming

Adding a time macro variable

Pabster — Mon, 18 Feb 2019 16:57:18 GMT

Hello y`all!

I stumble upon a tricky part of my program. I need to add a macro variable, or something similar, that will let me chose the gvkey that I want. For example I could chose the last one, the one before or even the first one. But it has to be done in one command.

For now I`ve manage to have the last one or the first one but I haven`t managed to add a SQL proc or a normal statement that let me chose whichever I want to see in my program (match2) for further analisys

Here is my code:

Data Last_Avail_Bnkrpt2;
set Inactive;
Last_Year=Fyear;
by gvkey;
if last.gvkey then output;
run;

proc sql;
create table Appareillement2 as
select distinct b.*
from Last_Avail_Bnkrpt2 as a, Active as b
where a.SICH=b.SICH and (b.fyear=(year(a.DLDTE)));
quit;

Data Last_Avail_Alive2;
set Appareillement2 ;
Last_Year=Fyear;
by gvkey;
if last.gvkey then output;
run;

data match2;
set Last_Avail_Alive2 Last_Avail_Bnkrpt2;
format DLDTE year4.;
run;

proc sort data=match2; by fyear sich ;run

Re: Adding a time macro variable

mkeintz — Mon, 18 Feb 2019 18:14:46 GMT

What is it that you are trying to do?
In the first step you are using "by gvkey" and "if last.gvkey". This does NOT provide the last gvkey. It provides the last record for each GVKEY. Since your data is probably sorted by gvkey and annual dates within gvkey, you're getting the most recent record for each gvkey.
Your are apparently using Compustat data (company identifier is GVKEY). Are you aware that FYEAR (as used in Compustat) is a fiscal-year, not a calendar year. If so, then for companies that use fiscal years other than Jan-Dec your proc SQL may not produce the match you want in the expression "(b.fyear=(year(a.DLDTE)))".
Also you are matching not on gvkey, but on SICH (historical SIC code - i.e. SIC code contemporary to DLDTE or FYEAR, I forget which). Are you intentionally doing a Cartesian crossing of all gvkeys that have matching fyear's and dldte's that are active in the same industry?

In short, I repeat - what are you trying to do? I.e. what data organization and content are you trying to produce?

Re: Adding a time macro variable

Pabster — Tue, 19 Feb 2019 20:30:52 GMT

To answer your questions:

1.What I am trying to do is add a variable that will let me decide which gvkey I want to see when the program runs
Right now my code tells SAS to provide the last record for each GVKEY. I would like to add a variable that let me change which GVKEY I’d see (the last one, the one before that, the 5thor the 3rd,etc..). Since the GVKEY will eventually take me to the year value I want to see (as you said the latest GVKEY is the most recent record, but I would like to be able to chose which record to see in order to properly analyse the data 1 or 2 or even 5 years prior the DLDTE date )

The end goal of the program is to be able to see a bankrupt company (witch DLDRSN= 02 or 03) that is inactive (COSTAT=1) on one line and a non-bankrupt company the line before from the same industry (SICH) and the same dates (FYEAR/DLDTE Year) or as close as possible this way :

DATADATE FYEAR DLRSN CONM DLDTE COSTAT SICH
03/12/1992 1989 . A 0 112
03/12/1993 1989 02 B 1990 1 112

03/12/1994 1990        .       D                        0           333
03/12/1992 1990        03    F         1992      1            333
03/12/1993 1994        .       J                         0          445
03/12/1994 1994        03    L         1996      1   445
03/12/1998 1998        .       I                         0           001
03/12/1998 1998        02    Z         2000      1            001
…..

2. Yes I did it on purpose, sorry if it wasn't clear on my first post.
3. Yes I do know that Fyear is the fiscal year and since I am dealing with company bankruptcy I need to take it into account , since actual years are not represented in financial ratios

4. I have to match by SICH or SIC or NAICS because I need the companies that I will compare in the future to be in the same industry

Re: Adding a time macro variable

Satish_Parida — Thu, 21 Feb 2019 08:36:41 GMT

The Issue Description is not to the point.
Can you describe what you have and what do you want as in table format.
And provide any sample code if you have tried.