how to create a dummy variable

[srikanthyadav44]

dear all,

i have the panel data of nearly 5000 companies from the year 2010 to 2019 in one file and other file consists of the year in which pubic issue of shares made by nearly 900 companies which belong to file one. 

i have to create a dummy variable with a condition. For example, if the "20 Microns Ltd" has made public issue in the year 2014,  the dummy variable should have value '1' in 2013(immediately preceding year) and zero in all the remaining years in the study period. In the same way, if "3I Infotech Ltd." has made public issue in the year 2016, the dummy variable should value '1' in the year 2015 and zero in all the remaining years. the format of the output file should be as follows 

company_name	year	IPO dummy
20 Microns Ltd.	2012	0
20 Microns Ltd.	2013	1
20 Microns Ltd.	2014	0
20 Microns Ltd.	2015	0
20 Microns Ltd.	2016	0
20 Microns Ltd.	2017	0
20 Microns Ltd.	2018	0
20 Microns Ltd.	2019	0
3I Infotech Ltd.	2010	0
3I Infotech Ltd.	2011	0
3I Infotech Ltd.	2012	0
3I Infotech Ltd.	2013	0
3I Infotech Ltd.	2014	0
3I Infotech Ltd.	2015	1
3I Infotech Ltd.	2016	0
3I Infotech Ltd.	2017	0
3I Infotech Ltd.	2018	0
3I Infotech Ltd.	2019	0

the two input files are in the following format 

Format of input file-1 (panel data of nearly 5000 companies from 2010 to 2019) 

company_name	year
20 Microns Ltd.	2012
20 Microns Ltd.	2013
20 Microns Ltd.	2014
20 Microns Ltd.	2015
20 Microns Ltd.	2016
20 Microns Ltd.	2017
20 Microns Ltd.	2018
20 Microns Ltd.	2019
3I Infotech Ltd.	2010
3I Infotech Ltd.	2011
3I Infotech Ltd.	2012
3I Infotech Ltd.	2013
3I Infotech Ltd.	2014
3I Infotech Ltd.	2015
3I Infotech Ltd.	2016
3I Infotech Ltd.	2017
3I Infotech Ltd.	2018
3I Infotech Ltd.	2019
3M India Ltd.	2010
3M India Ltd.	2011
3M India Ltd.	2012
3M India Ltd.	2013
3M India Ltd.	2014
3M India Ltd.	2015
3M India Ltd.	2016
3M India Ltd.	2017
3M India Ltd.	2018

format of input file-02 - (public issue made by the companies)

Company Name	IPO_Year
Bharatiya Global Infomedia Ltd.	2012
Brooks Laboratories Ltd.	2012
Flexituff Ventures Intl. Ltd.	2012
Future Consumer Ltd.	2012
Indo Thai Securities Ltd.	2012
Innoventive Industries Ltd.	2012
Inventure Growth & Securities Ltd.	2012
Kridhan Infra Ltd.	2012
L & T Finance Holdings Ltd.	2012
Muthoot Finance Ltd.	2012
Onelife Capital Advisors Ltd.	2012
P G Electroplast Ltd.	2012
Paramount Printpackaging Ltd.	2012
Power Finance Corpn. Ltd.	2012
R D B Rasayans Ltd.	2012
Rushil Decor Ltd.	2012
S R S Ltd.	2012
Sanghvi Forging & Engg. Ltd.	2012
Setubandhan Infrastructure Ltd.	2012
Shilpi Cable Technologies Ltd.	2012
T D Power Systems Ltd.	2012
Tijaria Polypipes Ltd.	2012

please suggest me a SAS code to create a dummy variable with the above discussed condition. 

thanks in advance. 

[PeterClemmensen · Accepted solution]

Here is how I would do it. Notice that I assume the company name variable is exactly company_name in both files. Also, I added the two companies "20 Microns Ltd." and "3I Infotech Ltd." to the file with IPO years for demonstration.

Feel free to ask 🙂

data one;
input company_name $ 1-16 year;
datalines;
20 Microns Ltd.  2012
20 Microns Ltd.  2013
20 Microns Ltd.  2014
20 Microns Ltd.  2015
20 Microns Ltd.  2016
20 Microns Ltd.  2017
20 Microns Ltd.  2018
20 Microns Ltd.  2019
3I Infotech Ltd. 2010
3I Infotech Ltd. 2011
3I Infotech Ltd. 2012
3I Infotech Ltd. 2013
3I Infotech Ltd. 2014
3I Infotech Ltd. 2015
3I Infotech Ltd. 2016
3I Infotech Ltd. 2017
3I Infotech Ltd. 2018
3I Infotech Ltd. 2019
3M India Ltd.    2010
3M India Ltd.    2011
3M India Ltd.    2012
3M India Ltd.    2013
3M India Ltd.    2014
3M India Ltd.    2015
3M India Ltd.    2016
3M India Ltd.    2017
3M India Ltd.    2018
;

data two;
input company_name $ 1-35 IPO_Year;
datalines;
Bharatiya Global Infomedia Ltd.    2012
Brooks Laboratories Ltd.           2012
Flexituff Ventures Intl. Ltd.      2012
Future Consumer Ltd.               2012
Indo Thai Securities Ltd.          2012
Innoventive Industries Ltd.        2012
Inventure Growth & Securities Ltd. 2012
Kridhan Infra Ltd.                 2012
L & T Finance Holdings Ltd.        2012
Muthoot Finance Ltd.               2012
Onelife Capital Advisors Ltd.      2012
P G Electroplast Ltd.              2012
Paramount Printpackaging Ltd.      2012
Power Finance Corpn. Ltd.          2012
R D B Rasayans Ltd.                2012
Rushil Decor Ltd.                  2012
S R S Ltd.                         2012
Sanghvi Forging & Engg. Ltd.       2012
Setubandhan Infrastructure Ltd.    2012
Shilpi Cable Technologies Ltd.     2012
T D Power Systems Ltd.             2012
Tijaria Polypipes Ltd.             2012
20 Microns Ltd.                    2014
3I Infotech Ltd.                   2016
;

data want;
   
   if _N_ = 1 then do;
      dcl hash h(dataset : "two(rename=IPO_Year=year)");
      h.definekey("company_name", "year");
      h.definedone();
   end;

   set one;

   dummy = h.check(key : company_name, key : year + 1) = 0;
   
run;

Result:

company_name      year  dummy 
20 Microns Ltd.   2012  0 
20 Microns Ltd.   2013  1 
20 Microns Ltd.   2014  0 
20 Microns Ltd.   2015  0 
20 Microns Ltd.   2016  0 
20 Microns Ltd.   2017  0 
20 Microns Ltd.   2018  0 
20 Microns Ltd.   2019  0 
3I Infotech Ltd.  2010  0 
3I Infotech Ltd.  2011  0 
3I Infotech Ltd.  2012  0 
3I Infotech Ltd.  2013  0 
3I Infotech Ltd.  2014  0 
3I Infotech Ltd.  2015  1 
3I Infotech Ltd.  2016  0 
3I Infotech Ltd.  2017  0 
3I Infotech Ltd.  2018  0 
3I Infotech Ltd.  2019  0 
3M India Ltd.     2010  0 
3M India Ltd.     2011  0 
3M India Ltd.     2012  0 
3M India Ltd.     2013  0 
3M India Ltd.     2014  0 
3M India Ltd.     2015  0 
3M India Ltd.     2016  0 
3M India Ltd.     2017  0 
3M India Ltd.     2018  0