downloading selected variables from merger database (SSH)
Posted: 26 June 2013 09:07 PM   [ Ignore ]
Newbie
Rank
Total Posts:  5
Joined  2013-06-20

As a WRDS newby I’d like to select data from the compustat crsp merger database.

I tried the text below on SSH (via kubuntu linux) and some things break the program:

1) when the number of vars grows. for some reason the keep list shall be limited, is this true? Ideally I’d like to upload a list of variables.
2) for permco’s I’d also like to submit a list, as the program below downloads all firms.
3) for some reason the export to xpt file fails, even though I use the example provided by WRDS.

Any help welcome!

Cheers,

Martien

=======================
libname mylib ‘/home/dummy/mummy/folder/’;

%let beg_yr = 2010;
%let end_yr = 2012;

PROC SQL;
  create table mydata (keep = acctstd aco acominc adjex_c adjex_f aocidergl aol2 ap apdedate aqpl1 artfs at au aul3 auop auopic capr1 capr2 capr3 caps ceoso ceq cfoso ch che ci cik cimii cogs compst conm consol costat csho cshr cstk curcd curncd cusip datadate datafmt dltt dp ds dvpsx_f dvt ebit ebitda emol emp epspi exchg fatb fatc fate fatl fatn fato fic fyear fyr gdwl gdwlam glp govtown gp gvkey ib ibmii indfmt intan intano invt ip ipc ipv iseq iseqc iseqm isfi isfxc isfxm islg islgc islgm islt isng isngc isngm isotc isoth isotm issc issm issu ist istc istm isut lct lol2 lqpl1 lt lul3 mib mibn mibt mkvalt ni oiadp oibdp opiti pddur popsrc ppegt ppenb prc prcc_c prcc_f prvt pstk pstkc pstkl pstkn pstkrv pvcl pvpl pvt rcl re rect ret rveqt rvlrv rvnt rvti seq sich src srt stbo teq tfva tfvl tic tlcf txdb txdba txdbca txdbcl txditc txndb txndba txndbl txndbr txp txpd txr txt xstfws)  as
  select a.*
  from COMPM.FUNDA a
  where
    indfmt=‘INDL’
  and datafmt=‘STD’
  and popsrc=‘D’
  and consol=‘C’
  and (&beg;_yr <= FYEAR <= &end;_yr)
  ;
quit;

proc export data=mydata dbms=dta outfile=’/home/dummy/mummy/ccm_a_001.dta’ replace ;
run;

proc export data=mydata dbms=csv outfile=’/home/dummy/mummy/ccm_a_001.csv’ replace ;
run;

libname out xport ‘/home/dummy/mummy/ccm_a_001.xpt’;
data out.mydata;
    set mylib.mydata;
run;

Profile
 
 
Posted: 27 June 2013 02:21 AM   [ Ignore ]   [ # 1 ]
Newbie
Rank
Total Posts:  5
Joined  2013-06-20

Found the solution, apart form the still not-working xport line, see attached.

File Attachments
ccmfunda_martien.sas  (File Size: 9KB - Downloads: 1327)
Profile
 
 
Posted: 27 June 2013 08:06 AM   [ Ignore ]   [ # 2 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Martien,

I am not familiar with the SAS export files; did you try something like this:

/* Convert from Transport File to SAS data file */
libname trans xport 'c:\QDS\my data\FirstQ.xpt';
libname demolib v8 'c:\QDS\my data';
proc copy in=trans out=demolib;
run

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 27 June 2013 05:21 PM   [ Ignore ]   [ # 3 ]
Newbie
Rank
Total Posts:  5
Joined  2013-06-20

Hi Joost,

Thanks for your suggestion. Not sure if your suggesitons work as I would like to export mydata to the xport file format.

I think it is a simple error on my side, something related to the assignment of libraries.

Here is the program lines, where the CSV export works just like a charm, but the xport to xpt not. See the error log further down

==================
proc export data=mydata dbms=csv outfile=’/home/.../...ccm_j_001.csv’ replace ;
run;
libname out xport ‘/home/.../...ccm_j_001.xpt’;
data out.mydata;
  set mylib.mydata;
run;
==================
367     libname out xport ‘/home/.../...ccm_j_001.xpt’;
NOTE: Libref OUT was successfully assigned as follows:
Engine:      XPORT
Physical Name: /home/.../...ccm_j_001.xpt
368     data out.mydata;
369         set mylib.mydata;
ERROR: File MYLIB.MYDATA.DATA does not exist.
370     run;
==================
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements.
    This might cause NOTE: No observations in data set.
WARNING: The data set OUT.MYDATA may be incomplete.  When this step was stopped
      there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):

Profile
 
 
Posted: 27 June 2013 08:04 PM   [ Ignore ]   [ # 4 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Martien,

Sorry - I now realize my suggestion was code for an import smile

I’m afraid I can’t help you with this one (as you probably already noticed) smile

best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile