Solved: Viewing tables without running old code

sastuck · Posted 04-24-2018 06:56 PM

Hello!

The title speaks for itself here. I would like to see output tables for investigative purposes but without having to go back and re-run the code that created the table to begin with. Is there a simple way to view the tables in my program?

Thanks!

-SAStuck

Reeza · Posted 04-24-2018 06:59 PM

If your processes are designed as such yes. This is the difference between a permanent and temporary data set.

Data set in WORK libraries do not exist once that session is closed. However, if you store you results in another library you can open those data sets again later as desired. So for data sets that you may want to view later, move them to the a permanent library at the end of your process.

@sastuck wrote:

Hello!

The title speaks for itself here. I would like to see output tables for investigative purposes but without having to go back and re-run the code that created the table to begin with. Is there a simple way to view the tables in my program?

Thanks!

-SAStuck

View solution in original post

Reeza · Posted 04-24-2018 06:59 PM

If your processes are designed as such yes. This is the difference between a permanent and temporary data set.

Data set in WORK libraries do not exist once that session is closed. However, if you store you results in another library you can open those data sets again later as desired. So for data sets that you may want to view later, move them to the a permanent library at the end of your process.

@sastuck wrote:

Hello!

The title speaks for itself here. I would like to see output tables for investigative purposes but without having to go back and re-run the code that created the table to begin with. Is there a simple way to view the tables in my program?

Thanks!

-SAStuck

sastuck · Posted 04-24-2018 08:24 PM

If this is the case, then which line of code would you recommend I run so as to recreate the work table (?) ceo_firm2 ? Here's the program:

libname paper "~/425/425_Final_Paper";

*import compustat dataset;
PROC IMPORT out=paper.CompuStat_Execucomp 
		datafile="~/425/425_Final_Paper/CompuStat_Execucomp.csv" DBMS=CSV replace;
	getnames=yes;
	guessingrows=2000;
run;

*keep only CEOs;
data paper.Compustat_ExecuComp2;
	set paper.Compustat_ExecuComp;
	if CEOANN='CEO';
run;

*** extra DATA step for checking previous data step results by comparing number of records selected;
data check_CEOANN;
	set paper.Compustat_ExecuComp;
	if CEOANN=: 'CEO';
run;

proc sort data=paper.Compustat_ExecuComp2;
	by ticker year;
run;

*import csrp dataset;
PROC IMPORT out=paper.CSRP_Monthly_Stock_char 
	datafile="~/425/425_Final_Paper/CSRP_MonthlyStock_char.csv" DBMS=CSV replace;
	getnames=yes;
	guessingrows=max;
run;

proc contents data=paper.CSRP_Monthly_Stock_char;
run;

*remove bad data;
data paper.CSRP_Monthly_Stock_char2;
	set paper.CSRP_Monthly_Stock_char (rename=(ret=character_ret));
	drop character_ret;
	
	if cusip=' ' then
		delete;
	ret=input(character_ret, ??8.);
	
	if ret=. then
		delete;
	date=input(put(date, z8.), yymmdd8.);
	format date yymmdd10.;
	year=year(date);
	month=month(date);

	if cusip=: '?' then
		cusip=substr(cusip, 2);
run;

proc contents data=paper.CSRP_Monthly_Stock_char;
run;

proc contents data=paper.CSRP_Monthly_Stock_char2;
run;

proc contents data=paper.multiple_CEOs;
run;

proc sort data=paper.CSRP_Monthly_Stock_char2;
	by ticker year;
run;

proc sort data=paper.Compustat_ExecuComp2;
	by ticker year;
run;

*Remove all bad years from both data sources;
data paper.Compustat_ExecuComp3;
	length ticker $5;
	merge paper.Compustat_ExecuComp2 paper.multiple_CEOs (keep=ticker year 
		in=had_multiple_CEOs);
	by ticker year;

	if had_multiple_CEOs then
		delete;
run;

data paper.CSRP_Monthly_Stock_char3;
	merge paper.CSRP_Monthly_Stock_char2 paper.multiple_CEOs (keep=ticker year 
		in=had_multiple_CEOs);
	by ticker year;

	if had_multiple_CEOs then
		delete;
run;

*find additional bad data: multiple return records for the same month/year;
proc freq data=paper.CSRP_Monthly_Stock_char3;
	tables ticker * year * month / noprint 
		out=paper.multiple_returns (where=(count > 1));
run;

*Remove all matching year data for multiple returns;
proc sort data=paper.multiple_returns out=multiple_returns (keep=ticker year) 
		NODUPKEY;
	by ticker year;
run;

data paper.Compustat_ExecuComp4;
	merge paper.Compustat_ExecuComp3 (in=keepme) 
		multiple_returns (in=had_multiple_returns);
	by ticker year;

	if keepme;

	if had_multiple_returns then
		delete;
run;

data paper.CSRP_Monthly_Stock_char4;
	length ticker $5;
	merge paper.CSRP_Monthly_Stock_char3 
		multiple_returns (in=had_multiple_returns);
	by ticker year;

	if had_multiple_returns then
		delete;
run;

proc contents data=paper.CSRP_Monthly_Stock_char3;
run;

proc contents data=paper.CSRP_Monthly_Stock_char4;
run;

*create new variable annualized growth;
data paper.CSRP_annual_returns;
	set paper.CSRP_Monthly_Stock_char4;
	by ticker year;
	retain annual_return 1;
	annual_return=annual_return * (1 + RET);

	if month=12 or last.ticker;
	annual_return=(annual_return - 1) * 100;
	output;
	annual_return=1;
	keep ticker year annual_return;
run;

*use proc contents to see if there is a type mismatch;
proc contents data=paper.CSRP_annual_returns;
run;

proc contents data=paper.Compustat_ExecuComp4;
run;

*MERGE statement has more than one data set with repeats of BY values;
proc sort data=paper.CSRP_annual_returns out=paper.CSRP_annual_returns2 
		nodupkey;
	by ticker;
run;

*Merge CEO data and firm data;
DATA paper.ceo_firm;
	length ticker $5;
	merge paper.CSRP_annual_returns2 (in=in1) paper.compustat_execucomp4 (in=in2);
	by ticker;

	if in1 and in2;
run;

proc contents data=paper.ceo_firm;
run;

proc contents data=paper.ceo_firm2;
run;

*remove missing return data;
data paper.ceo_firm;
	set paper.ceo_firm;

	if annual_return=. then
		delete;
run;

proc means data=paper.CSRP_Monthly_Stock_char3 n nmiss;
	var ret;
run;

proc means data=paper.Compustat_ExecuComp4 n nmiss;
	var salary;
run;

proc means data=paper.CSRP_annual_returns n nmiss;
	var annual_return;
run;

data paper.ceo_firm;
	set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
	BONUS=input(_BONUS, ?? 8.);
	STOCK_AWARDS=input(_STOCK_AWARDS, ?? 8.);
run;

data paper.ceo_firm2;
	set paper.ceo_firm2 (rename=(othcomp=_othcomp));
	othcomp=input(_othcomp, ?? 8.);
run;

*create dummy variable that indicates whether the firm's market value increases in a given year;
data paper.ceo_firm;
	set paper.ceo_firm;
	by ticker year;
	last_return=lag(annual_return);

	if not first.ticker then
		increase=(annual_return >  last_return);
	else
		increase=.;
run;

*create dummy variable that indicates whether the CEO is male or female;
data paper.ceo_firm;
	set paper.ceo_firm;
	if gender='MALE' then male=1; else male=0;
run; 

*bring variable in from other data set; 
proc sql;
     create table paper.ceo_firm2 as 
      select  a.* , b.VOL
      from paper.ceo_firm as a
      left join
     (select ticker
                ,year
                ,sum(VOL) as VOL
      from paper.CSRP_Monthly_Stock_char4
      group by ticker
              ,year
     ) as b
     on a.ticker = b.ticker and
     a.year = b.year;
quit;

/* CHECK FOR DUPLICATES */
data is_dup;
 set paper.CSRP_Monthly_Stock_char4;    /* dataset origin containing var to add VOL ? */
  by ticker year;
     if not (first.year and last.year);
run;

*find duplicates: ticker/year combo;
proc freq data=paper.CSRP_Monthly_Stock_char4;
	tables ticker * year / noprint 
		out=paper.multiple_tyCSRP (where=(count > 1));
run;

proc freq data=paper.ceo_firm;
	tables ticker * year / noprint 
		out=paper.multiple_tyfirm (where=(count > 1));
run;

*create variable log_salary;
data paper.ceo_firm;
	set paper.ceo_firm;
	if salary=0 then log_salary=0;
	else log_salary=log(salary);
run;

*create variable log_annual_return;
data paper.ceo_firm;
	set paper.ceo_firm;
	if annual_return=0 then log_annual_return=0;
	else log_annual_return=log(annual_return);
run;

*create variable log_bonus;
data paper.ceo_firm;
	set paper.ceo_firm;
	if bonus=0 then log_bonus=0;
	else log_bonus=log(bonus);
run;

*create variable log_TOTAL_SEC;
data paper.ceo_firm;
	set paper.ceo_firm;
	if TOTAL_SEC=0 then log_TOTAL_SEC=0;
	else log_TOTAL_SEC=log(TOTAL_SEC);
run;

*create interaction term;
data paper.ceo_firm2;
	set paper.ceo_firm2;
	annual_return_VOL=(annual_return*VOL);
run;

Thanks!

-SAStuck

Reeza · Posted 04-24-2018 10:46 PM

1. Assign the library:

libname paper "~/425/425_Final_Paper";

2. Open the data set:

proc print data=paper.ceo_firm2 (obs=5);
run;

@sastuck wrote:

If this is the case, then which line of code would you recommend I run so as to recreate the work table (?) ceo_firm2 ? Here's the program:

libname paper "~/425/425_Final_Paper";

*import compustat dataset;
PROC IMPORT out=paper.CompuStat_Execucomp 
		datafile="~/425/425_Final_Paper/CompuStat_Execucomp.csv" DBMS=CSV replace;
	getnames=yes;
	guessingrows=2000;
run;

*keep only CEOs;
data paper.Compustat_ExecuComp2;
	set paper.Compustat_ExecuComp;
	if CEOANN='CEO';
run;

*** extra DATA step for checking previous data step results by comparing number of records selected;
data check_CEOANN;
	set paper.Compustat_ExecuComp;
	if CEOANN=: 'CEO';
run;

proc sort data=paper.Compustat_ExecuComp2;
	by ticker year;
run;

*import csrp dataset;
PROC IMPORT out=paper.CSRP_Monthly_Stock_char 
	datafile="~/425/425_Final_Paper/CSRP_MonthlyStock_char.csv" DBMS=CSV replace;
	getnames=yes;
	guessingrows=max;
run;

proc contents data=paper.CSRP_Monthly_Stock_char;
run;

*remove bad data;
data paper.CSRP_Monthly_Stock_char2;
	set paper.CSRP_Monthly_Stock_char (rename=(ret=character_ret));
	drop character_ret;
	
	if cusip=' ' then
		delete;
	ret=input(character_ret, ??8.);
	
	if ret=. then
		delete;
	date=input(put(date, z8.), yymmdd8.);
	format date yymmdd10.;
	year=year(date);
	month=month(date);

	if cusip=: '?' then
		cusip=substr(cusip, 2);
run;

proc contents data=paper.CSRP_Monthly_Stock_char;
run;

proc contents data=paper.CSRP_Monthly_Stock_char2;
run;

proc contents data=paper.multiple_CEOs;
run;

proc sort data=paper.CSRP_Monthly_Stock_char2;
	by ticker year;
run;

proc sort data=paper.Compustat_ExecuComp2;
	by ticker year;
run;

*Remove all bad years from both data sources;
data paper.Compustat_ExecuComp3;
	length ticker $5;
	merge paper.Compustat_ExecuComp2 paper.multiple_CEOs (keep=ticker year 
		in=had_multiple_CEOs);
	by ticker year;

	if had_multiple_CEOs then
		delete;
run;

data paper.CSRP_Monthly_Stock_char3;
	merge paper.CSRP_Monthly_Stock_char2 paper.multiple_CEOs (keep=ticker year 
		in=had_multiple_CEOs);
	by ticker year;

	if had_multiple_CEOs then
		delete;
run;

*find additional bad data: multiple return records for the same month/year;
proc freq data=paper.CSRP_Monthly_Stock_char3;
	tables ticker * year * month / noprint 
		out=paper.multiple_returns (where=(count > 1));
run;

*Remove all matching year data for multiple returns;
proc sort data=paper.multiple_returns out=multiple_returns (keep=ticker year) 
		NODUPKEY;
	by ticker year;
run;

data paper.Compustat_ExecuComp4;
	merge paper.Compustat_ExecuComp3 (in=keepme) 
		multiple_returns (in=had_multiple_returns);
	by ticker year;

	if keepme;

	if had_multiple_returns then
		delete;
run;

data paper.CSRP_Monthly_Stock_char4;
	length ticker $5;
	merge paper.CSRP_Monthly_Stock_char3 
		multiple_returns (in=had_multiple_returns);
	by ticker year;

	if had_multiple_returns then
		delete;
run;

proc contents data=paper.CSRP_Monthly_Stock_char3;
run;

proc contents data=paper.CSRP_Monthly_Stock_char4;
run;

*create new variable annualized growth;
data paper.CSRP_annual_returns;
	set paper.CSRP_Monthly_Stock_char4;
	by ticker year;
	retain annual_return 1;
	annual_return=annual_return * (1 + RET);

	if month=12 or last.ticker;
	annual_return=(annual_return - 1) * 100;
	output;
	annual_return=1;
	keep ticker year annual_return;
run;

*use proc contents to see if there is a type mismatch;
proc contents data=paper.CSRP_annual_returns;
run;

proc contents data=paper.Compustat_ExecuComp4;
run;

*MERGE statement has more than one data set with repeats of BY values;
proc sort data=paper.CSRP_annual_returns out=paper.CSRP_annual_returns2 
		nodupkey;
	by ticker;
run;

*Merge CEO data and firm data;
DATA paper.ceo_firm;
	length ticker $5;
	merge paper.CSRP_annual_returns2 (in=in1) paper.compustat_execucomp4 (in=in2);
	by ticker;

	if in1 and in2;
run;

proc contents data=paper.ceo_firm;
run;

proc contents data=paper.ceo_firm2;
run;

*remove missing return data;
data paper.ceo_firm;
	set paper.ceo_firm;

	if annual_return=. then
		delete;
run;

proc means data=paper.CSRP_Monthly_Stock_char3 n nmiss;
	var ret;
run;

proc means data=paper.Compustat_ExecuComp4 n nmiss;
	var salary;
run;

proc means data=paper.CSRP_annual_returns n nmiss;
	var annual_return;
run;

data paper.ceo_firm;
	set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
	BONUS=input(_BONUS, ?? 8.);
	STOCK_AWARDS=input(_STOCK_AWARDS, ?? 8.);
run;

data paper.ceo_firm2;
	set paper.ceo_firm2 (rename=(othcomp=_othcomp));
	othcomp=input(_othcomp, ?? 8.);
run;

*create dummy variable that indicates whether the firm's market value increases in a given year;
data paper.ceo_firm;
	set paper.ceo_firm;
	by ticker year;
	last_return=lag(annual_return);

	if not first.ticker then
		increase=(annual_return >  last_return);
	else
		increase=.;
run;

*create dummy variable that indicates whether the CEO is male or female;
data paper.ceo_firm;
	set paper.ceo_firm;
	if gender='MALE' then male=1; else male=0;
run; 

*bring variable in from other data set; 
proc sql;
     create table paper.ceo_firm2 as 
      select  a.* , b.VOL
      from paper.ceo_firm as a
      left join
     (select ticker
                ,year
                ,sum(VOL) as VOL
      from paper.CSRP_Monthly_Stock_char4
      group by ticker
              ,year
     ) as b
     on a.ticker = b.ticker and
     a.year = b.year;
quit;

/* CHECK FOR DUPLICATES */
data is_dup;
 set paper.CSRP_Monthly_Stock_char4;    /* dataset origin containing var to add VOL ? */
  by ticker year;
     if not (first.year and last.year);
run;

*find duplicates: ticker/year combo;
proc freq data=paper.CSRP_Monthly_Stock_char4;
	tables ticker * year / noprint 
		out=paper.multiple_tyCSRP (where=(count > 1));
run;

proc freq data=paper.ceo_firm;
	tables ticker * year / noprint 
		out=paper.multiple_tyfirm (where=(count > 1));
run;

*create variable log_salary;
data paper.ceo_firm;
	set paper.ceo_firm;
	if salary=0 then log_salary=0;
	else log_salary=log(salary);
run;

*create variable log_annual_return;
data paper.ceo_firm;
	set paper.ceo_firm;
	if annual_return=0 then log_annual_return=0;
	else log_annual_return=log(annual_return);
run;

*create variable log_bonus;
data paper.ceo_firm;
	set paper.ceo_firm;
	if bonus=0 then log_bonus=0;
	else log_bonus=log(bonus);
run;

*create variable log_TOTAL_SEC;
data paper.ceo_firm;
	set paper.ceo_firm;
	if TOTAL_SEC=0 then log_TOTAL_SEC=0;
	else log_TOTAL_SEC=log(TOTAL_SEC);
run;

*create interaction term;
data paper.ceo_firm2;
	set paper.ceo_firm2;
	annual_return_VOL=(annual_return*VOL);
run;

Thanks!

-SAStuck

ballardw · Posted 04-24-2018 07:10 PM

This might be time to clarify what you mean by "table" in this context. If you mean a SAS data set then I think @Reeza has covered the basic bases.

However if by "table" you mean a results table such as from a procedure such as Freq, Means, Reg et. al. then you need to explicitly save the results as generated. Either use of ODS destination statements or Proc Document to save a document composed of multiple outputs in the Results window.

If something else then more details please.

Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Re: Viewing tables without running old code

Click image to register for webinar

Classroom Training Available!