BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ericdrosano
Obsidian | Level 7

Table 1 in SAS screen capture.png

 

I have the above data in a table in SAS; there are a total of 441 unique observations. I want to use GKVEY and FILEYEAR (table variables), matched to gvkey and datadate (COMPUSTAT variables), to pull other information (e.g. CHE, AT, etc.)  about each matched observation within COMPUSTAT from WRDS, remotely.

 

How do I do this?

 

Thank you for your assistance, and I apologize if I am not explaining correctly; I'm as newb as newb gets. 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
ericdrosano
Obsidian | Level 7

I was able to arrive at a solution.

 

The following is the code I used:

 

rsubmit;
proc sql;
	create table want
	as select
		try.namea, try.ind, try.gvkey, try.fileyear, try.score,
		funda.gvkey, funda.datadate, funda.fyear,
		funda.sale, funda.at, funda.che, funda.prcc_f, funda.ceq, funda.lt, funda.xrd, funda.bkvlps, funda.csho
	from home.try, compa.funda
	where funda.gvkey = try.gvkey
	and funda.fyear = try.fileyear;
quit;

proc print data=home.want;
run;

View solution in original post

5 REPLIES 5
Cynthia_sas
SAS Super FREQ

Hi:

  You can get help with WRDS by filling out this form on the Wharton site: https://wrds-web.wharton.upenn.edu/wrds/about/external_support_request.cfm

 

  In a quick Google search of the site, it appears that they have a form you have to fill out to get Compustat data as shown below:

wrds_form.png

 

so depending on what you want/need to do, you may need to work with WRDS support to see if they have some kind of API where you can submit a request to extract the data you want by using your values from the SAS table as  input to some program on their site.

 

Unless someone here has experience with WRDS and doing what you need to do, you might be better off approaching this from the WRDS side for support.

 

My only suggestion would be to get the Compustat data for all of your desired years (for all GVKEYS) into SAS and then merge or join with your table (in SAS) based on the GVKEY to select only the rows you want...but that is assuming that your goal is to get all the data into SAS and do your analysis in SAS. How to navigate and extract from WRDS would be something to address using their documentation for how to use their site: https://wrds-www.wharton.upenn.edu/pages/3-ways-use-wrds/#command-line-access

 

cynthia

ericdrosano
Obsidian | Level 7

Thank you for your reply, but I already know how to pull the data directly from the site; that's not what I'm asking for assistance with. If I can code this same process into SAS that will allow me to avoid using UPenn's website, and create my own programming "module" that can be modified for any new data request I might have in the future.

mkeintz
PROC Star

Assuming you have a WRDS account, you can use the WRDS compustat web query to download the data of interest.  Since you have over 400 GVKEYs you don't  want to enter them in a form, so make a text file on your PC with those GVKEYs (one line per gvkey). Then use the web query option that allows you to specify that pc file for uploading to the web query.

 

This is all in the "how to use WRDS" web pages available when you login to WRDS on your wrds account.  The only SAS issue I see at this point is  to make the text file of gvkeys from your SAS file (says it's data set HAVE):

 

data _null_;

  file "c:\temp\mygvkeys.txt";

  set have;

  put gvkey;

run;

 

You then can drive the web query with file   c:\temp\mygvkey.txt

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ericdrosano
Obsidian | Level 7

Thank you, I'll give this a shot, and let you know how it goes.  I will have to do a great deal of modifying, but I think this might be the best path to achieving what I've asked for.

ericdrosano
Obsidian | Level 7

I was able to arrive at a solution.

 

The following is the code I used:

 

rsubmit;
proc sql;
	create table want
	as select
		try.namea, try.ind, try.gvkey, try.fileyear, try.score,
		funda.gvkey, funda.datadate, funda.fyear,
		funda.sale, funda.at, funda.che, funda.prcc_f, funda.ceq, funda.lt, funda.xrd, funda.bkvlps, funda.csho
	from home.try, compa.funda
	where funda.gvkey = try.gvkey
	and funda.fyear = try.fileyear;
quit;

proc print data=home.want;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1750 views
  • 0 likes
  • 3 in conversation