turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- More efficient way

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-18-2012 11:05 PM

Dear all,

I ran the following program (propensity score matching):

proc sort data=test out=full ;

by descending tem; run;

proc probit data=FULL noprint;

class TEM;

model TEM = FREQ SHARES NUMEST BONUS_PERCENT1 BLACK_OPTION1 ROA1 LG_ASSET MB

IND01-IND10 YeaR03 - YeaR09 / lackfit;

output out=regdata_out xbeta=gammaw p=pred;

run;

data GD1 GD0; set regdata_out;

if TEM = 1 then output GD1;

if TEM = 0 then output GD0;

run;

proc sql;

create table temp as select a.tic as TEM1_tic, a.fyear as tem1_fyear, b.*, abs(sum(a.pred, -b.pred)) as diffPS,

min(calculated diffPS) as closest_PS

from GD1 a , GD0 b

group by a.tic, a.fyear

having diffPS = closest_PS;

quit;

proc sql;

create table temp_tem1 as select a.*

from GD1 a, temp b

where

a.tic = b.TEM1_tic and a.fyear = b.tem1_fyear;

quit;

data matchpair; set temp_tem1 temp(drop = tem1_tic); run;

But I could not get the output as it consumes a lot of processing time and a large storage. I am not sure whether there is a more efficient way to obtain the same output as the program above?

Thank you.

Regards,

mspak

Accepted Solutions

Solution

09-20-2012
01:21 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-20-2012 01:21 AM

Well. After through the logistic Model , I found a lot of missing value of pred in both GD0 and GD1 , maybe you do not need it any more , exclude them , it will be very fast.

proc sql; create table temp as select a.tic as TEM1_tic, a.fyear as tem1_fyear, b.*, abs(a.pred - b.pred) as diffPS from GD1(where=(pred is not missing)) a , GD0(where=(pred is not missing)) b group by a.tic, a.fyear having calculated diffPS = min(calculated diffPS); quit;

Ksharp

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2012 01:08 AM

You are using Cartesian Product of SQL . therefore, you almost can't get the result when you have a not big table.

What is your sample data?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2012 10:36 PM

Sorry for late reply. I was experiencing the problem to upload my SAS file. I have updated my query.

Thanks for suggestions.

Mspak

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2012 12:19 PM

In your first join, you're not specifying any join keys. Because of this, every record in GD1 will be matched with every record in GD0 to create your result, which is probably not what you want.

There are different synaxes for doing this, I recommend the form of:

proc sql;

create table temp as select ...

from GD1 a inner join GD0 b

on a.join_key = b.join_key

...

where join_key represents the fields in GD1 and GD0 that can be used to match the tables.

On the other hand, if you genuinely do need to do a cartesian join, if you provide more details of your objectives I'm sure the group will be able to help improve the speed.

Tom

P.S. If you drop the following string into Google, the first result should take you right to the relevante section of the documentation:

Cartesian Product of LEFTTAB and RIGHTTAB Tables site:support.sas.com

T

Message was edited by: Tom Kari

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-20-2012 01:20 AM

Dear Tom Kari,

Thanks for your suggestion.

I am not going to join with a joining key. I would like to match GD1 and GD0 with the closest_PS. I wish to pair GD1 and GD0 with their closest score (ie. closest_PS).

Regards,

MSPAK

Solution

09-20-2012
01:21 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-20-2012 01:21 AM

Well. After through the logistic Model , I found a lot of missing value of pred in both GD0 and GD1 , maybe you do not need it any more , exclude them , it will be very fast.

proc sql; create table temp as select a.tic as TEM1_tic, a.fyear as tem1_fyear, b.*, abs(a.pred - b.pred) as diffPS from GD1(where=(pred is not missing)) a , GD0(where=(pred is not missing)) b group by a.tic, a.fyear having calculated diffPS = min(calculated diffPS); quit;

Ksharp

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-20-2012 08:54 AM

Lots of missing data in pred is a red flag for me. That means that those observations are not going to be used in the propensity score matching process (as made explicit in KSharp's code). You may wish to look at the patterns of missing in the predictors to see if you can refine the initial model.

Doc Muhlbaier

Duke

Message was edited by: Lawrence Muhlbaier See this note for lots on how to do the matching more efficiently http://support.sas.com/kb/30/971.html

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-22-2012 07:06 AM

Thanks you for your suggestion.

It does work.

Regards,

mspak