how to combine specific datasets

[Radwan]

Hello everyone,

My question this time is different which is 

of course my dependent variable is expressed by one value if the firm misstated the financial statement zero otherwise. I have the datafile which contains only the firms that have misstated the financial statement( means with one value) in the meantime i have the data file for all firms that belong to independent  and control variables. but how can i match the firm that misstated with non_misstated firms/

because when i run the regression model by first file the result comes out as omitted variables.

 please i need to link misstated firms with non_misstated firm in order to be analyzable. 

I hope you get my point and you have a solution.  

thanks in advance 

[Amir]

Perhaps if you provided some example input and output data in the form of a data step, then that might help our understanding of what you require.

If you have tried something and are getting an error then please share the code and log.

If this is related to another question then please share a link to that question too.

Regards,

Amir.

[Radwan]

thanks @Amir 

i did not try any thing because I knew it when i was hepping someone in his analysis, so I have not try because the mean of my dependent variable is one since i have no zero value.

[Kurt_Bremser]

See my footnotes for help in preparing the data step code for posting your example data here.

[Radwan]

Hello @Kurt_Bremser 

I have done as the instructions say and i got this message in the log tap

DATA2DATASTEP DSN VIOLATION
DATA2DATASTEP FILE create_WORK_VIOLATION_data.sas
DATA2DATASTEP FMT YES
DATA2DATASTEP FMTLIST code BEST. year BEST. SumPe0alt1 BEST. penalty_amount BEST.
DATA2DATASTEP INPUTLIST code:BEST. year:BEST. Violatio0T1pe:$182. Violatio0T1peID:$41. SumPe0alt1:BEST. Pu0ishme0tT1pe:$54. Pu0ishme0tT1peID:$23. penalty_amount:BEST.
DATA2DATASTEP LBL YES
DATA2DATASTEP LBLLIST code="code" year="year" Violatio0T1pe="Violatio0T1pe" Violatio0T1peID="Violatio0T1peID" SumPe0alt1="SumPe0alt1" Pu0ishme0tT1pe="Pu0ishme0tT1pe" Pu0ishme0tT1peID="Pu0ishme0tT1peID" penalty_amount="penalty_amount"
DATA2DATASTEP LIB WORK
DATA2DATASTEP LS 256
DATA2DATASTEP MEMLABEL
DATA2DATASTEP MSGTYPE NOTE
DATA2DATASTEP OBS 20
DATA2DATASTEP OUTLIB WORK
DATA2DATASTEP VARLIST code year Violatio0T1pe Violatio0T1peID SumPe0alt1 Pu0ishme0tT1pe Pu0ishme0tT1peID penalty_amount

data WORK.VIOLATION;
infile datalines dsd truncover;
input code:BEST. year:BEST. Violatio0T1pe:$182. Violatio0T1peID:$41. SumPe0alt1:BEST. Pu0ishme0tT1pe:$54. Pu0ishme0tT1peID:$23. penalty_amount:BEST.;
format code BEST. year BEST. SumPe0alt1 BEST. penalty_amount BEST.;
label code="code" year="year" Violatio0T1pe="Violatio0T1pe" Violatio0T1peID="Violatio0T1peID" SumPe0alt1="SumPe0alt1" Pu0ishme0tT1pe="Pu0ishme0tT1pe" Pu0ishme0tT1peID="Pu0ishme0tT1peID" penalty_amount="penalty_amount";
datalines;
3 2001 deferred discolsure P2504 . Condmn P2603 .
3 2002 other P2599 . criticism P2601 .
4 2002 deferred discolsure P2504 . Condmn P2603 .
4 2010 False record(Misleading statements), P2503,P2599 . criticism P2601 .
4 2010 Major omissions,False disclosure(), P2505,P2506,P2599 . .
4 2013 False record(Misleading statements),Improper handling of general accounting,other P2503,P2515,P2599 . other P2699 .
4 2018 deferred discolsure P2504 580000 warning,forfeit P2602,P2604 400000
5 2018 deferred discolsure P2504 . other P2699 .
6 2010 False record(Misleading statements),Deferred disclosure,Major omissions,other P2503,P2504,P2505,P2599 . other P2699 .
7 2010 Illegal trading of stocks P2512 . .
7 2008 deferred discolsure P2504 . .
7 2012 False records (misleadi0g stateme0ts) P2503 . .
7 2013 Major omissions,other P2505,P2599 . criticism P2601 .
7 2014 False record(Misleading statements),Major omissions, P2503,P2505,P2599 800000 warning,forfeit P2602,P2604 400000
7 2014 False record(Misleading statements),Deferred disclosure, P2503,P2504,P2599 . Condmn P2603 .
7 2014 False record(Misleading statements),Major omissions,other P2503,P2505,P2599 . other P2699 .
7 2015 Deferred disclosure, P2504,P2599 . criticism P2601 .
7 2015 Major omissions,other P2505,P2599 600000 .
7 2015 Deferred disclosure,employ firm assets,other P2504,P2510,P2599 . other P2699 .
7 2016 Deferred disclosure,Major omissions, P2504,P2505,P2599 . .
;;;;
NOTE: There were 20 observations read from the data set WORK.VIOLATION.
NOTE: DATA statement used (Total process time):
real time 0.23 seconds
cpu time 0.09 seconds

@Amir 

[Radwan]

you are gonna see normal observations 

my dataset contains over 5000 obs, all those obs represent the firm that misstated the financial statement only so when i combine it with the rest of my dataset for independent and control variables the program is gonna consider the final observations with one(1) value of my dependent variable. then when i run my regression model the result comes out zero or as omitted variable because the mean of my dependent variable is 1.

so in this case i need combine this file with the firms with zero value.

according to so, how can i combine it where my dependent variable contains two values (1= misstated firm, 0= none).

i hope you understand my issue.

@Kurt_Bremser 

@Amir 

Thanks

[Kurt_Bremser]

Please post the contents of file

create_WORK_VIOLATION_data.sas

into a window opened with the "little running man" icon, or attach the file to your post.

[Radwan]

Do you mean this 

[Kurt_Bremser]

No, the data2datastep macro creates a program (.sas) file that contains the runnable code.

When I do this

%data2datastep(dsn=class,lib=sashelp,file=class.sas,obs=10)

I get a file class.sas in my home directory on the SAS server, which contains:

data SASHELP.CLASS;
  infile datalines dsd truncover;
  input Name:$8. Sex:$1. Age:32. Height:32. Weight:32.;
datalines4;
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
;;;;

 We need this, not an excerpt from the log.

[Radwan]

data WORK.VIOLATION;
infile datalines dsd truncover;
input code:BEST. year:BEST. Violatio0T1pe:$182. Violatio0T1peID:$41. SumPe0alt1:BEST. IsViolated:BEST. Pu0ishme0tT1pe:$54. Pu0ishme0tT1peID:$23. penalty_amount:BEST.;
format code BEST. year BEST. SumPe0alt1 BEST. IsViolated BEST. penalty_amount BEST.;
label code="code" year="year" Violatio0T1pe="Violatio0T1pe" Violatio0T1peID="Violatio0T1peID" SumPe0alt1="SumPe0alt1" IsViolated="IsViolated" Pu0ishme0tT1pe="Pu0ishme0tT1pe" Pu0ishme0tT1peID="Pu0ishme0tT1peID" penalty_amount="penalty_amount";
datalines;
3 2001 deferred discolsure P2504 . 1 Condmn P2603 .
3 2002 other P2599 . 1 criticism P2601 .
4 2002 deferred discolsure P2504 . 1 Condmn P2603 .
4 2010 False record(Misleading statements), P2503,P2599 . 1 criticism P2601 .
4 2010 Major omissions,False disclosure(), P2505,P2506,P2599 . 0 .
4 2013 False record(Misleading statements),Improper handling of general accounting,other P2503,P2515,P2599 . 1 other P2699 .
4 2018 deferred discolsure P2504 580000 1 warning,forfeit P2602,P2604 400000
5 2018 deferred discolsure P2504 . 1 other P2699 .
6 2010 False record(Misleading statements),Deferred disclosure,Major omissions,other P2503,P2504,P2505,P2599 . 1 other P2699 .
7 2010 Illegal trading of stocks P2512 . 0 .
7 2008 deferred discolsure P2504 . 0 .
7 2012 False records (misleadi0g stateme0ts) P2503 . 0 .
7 2013 Major omissions,other P2505,P2599 . 1 criticism P2601 .
7 2014 False record(Misleading statements),Major omissions, P2503,P2505,P2599 800000 1 warning,forfeit P2602,P2604 400000
7 2014 False record(Misleading statements),Deferred disclosure, P2503,P2504,P2599 . 1 Condmn P2603 .
7 2014 False record(Misleading statements),Major omissions,other P2503,P2505,P2599 . 1 other P2699 .
7 2015 Deferred disclosure, P2504,P2599 . 1 criticism P2601 .
7 2015 Major omissions,other P2505,P2599 600000 0 .
7 2015 Deferred disclosure,employ firm assets,other P2504,P2510,P2599 . 1 other P2699 .
7 2016 Deferred disclosure,Major omissions, P2504,P2505,P2599 . 0 .
7 2015 False disclosure(),Major omissions, P2506,P2505,P2599 980000 1 warning,forfeit P2602,P2604 600000
7 2016 deferred discolsure P2504 800000 1 warning,forfeit P2602,P2604 300000
9 2016 False record(Misleading statements),Deferred disclosure,Major omissions, P2503,P2504,P2505,P2599 . 1 Condmn P2603 .
9 2002 deferred discolsure P2504 . 1 criticism P2601 .
9 2003 Deferred disclosure, P2504,P2599 . 1 criticism P2601 .
9 2011 False records (misleading statements) P2503 . 1 criticism P2601 .
10 2018 False disclosure(),Improper handling of general accounting P2506,P2515 . 1 other P2699 .
10 2009 False record(Misleading statements),Deferred disclosure,Major omissions, irregularities and guarantees,Improper handling of general accounting,other P2503,P2504,P2505,P2514,P2515,P2599 . 1 other P2699 .
10 2010 False record(Misleading statements),Major omissions,employ firm assets P2503,P2505,P2510 . 1 criticism P2601 .
10 2011 Deferred disclosure,False disclosure(),employ firm assets,Improper handling of general accounting,other P2504,P2506,P2510,P2515,P2599 . 1 other P2699 .
;;;;

Hello @Kurt_Bremser 

I hope it is done this time 

[Kurt_Bremser · Accepted solution]

I have found a problem with the macro as posted on github. Line 132 is commented out with an asterisk; remove that asterisk, rerun the macro definition, and then it should honor the file= parameter and write the external program file. The code in the log does not contain the delimiting commas in the datalines block that are necessary for it to work properly.