Good evening,
This is my first time to use SAS and also my first time to ask a question in this forum. Thank you all first for your patient and help.
I have the following data (sample):
obs id listing_1 listing_2 selling_1 selling_2 status date_sign la1_no_la1_5 la1_no_la2_5
1 1 A B C D sold 2012-01-23 00:00:12 1 2
2 2 C . D . sold 2007-05-01 00:01:15 0 0
3 3 E F A C sold 2013-03-05 08:21:05 1 0
4 4 B A . . expired 2011-01-15 05:21:00 0 0
5 5 A C B F sold 2008-05-11 00:00:00 0 0
6 6 E . D . sold 2011-07-15 00:23:15 0 0
7 7 C A A . sold 2012-11-26 00:15:00 1 1
8 8 C . C F sold 2007-12-15 00:17:25 1 0
9 9 F B B C sold 2014-12-01 00:00:00 0 1
10 10 D A . . expired 2011-12-14 00:15:25 0 0
The last two columns are the results or new variables I want to create.
I have 10 observations, each observation is a transaction. For each transaction, at most two listing agents (listing_1 and listing_2) and two selling agents (selling_1 and selling_2) involved. Each id, no matter get sold or go expired, has a date_sign, which is the date the contract signature date between the listing agents and the client. I want to create two new variables: la1_no_la1_5 and la1_no_la2_5 to record the first listing agent's past activities. la1_no_la1_5 represents the total number of transactions (both sold and expired) the first listing agent involved as a first listing agent in the past five years of the signature date. For example, for obs1, la1_no_la1_5= 1 means the first listing agent of id=1 transaction involved in 1 transaction as a first listing agent in the past 5 years of the signature date (2012-01-23 00:00:12 ).
The process behind I want to create a loop that for each id, the system finds its first listing agent, A in the above example, then look up all the id's (from obs1 to obs10) first listing agents and find the first listing agent = A, then compare the date_sign. if date_sign is within five years, +1 to la1_no_la1_5.
I used stata to successfully calculate the numbers, but because my full data is too large, almost 1.5 million transactions, stata takes too long to process the code.
Following are my stata code:
gen la1_no_la1_5 = .
local N = _N
forvalues i = 1(1)`N' {
count if listing_1[`i']==listing_1 & (date_sign[`i'] - date_sign)/365.25 <= 5 &(date_sign[`i'] - date_sign)/365.25 > 0
replace la1_no_la1_5 = r(N) in `i'
}
Can anyone help me to translate the stata code to useful sas code or help me to write a new sas code to process the above calculation? I really appreciate.
Thank you again
Freda
It does not create new file
Add create table at the start of the query
When you say "within five years", does that mean looking back five years?
Like this?
proc sql;
select unique have.*, count(past1.date_sign) as la1_no_la1_5
from have
left join
have past1
on have.listing_1=past1.listing_1
and 0 < have.date_sign-past1.date_sign < 5*365.25
group by 1
order by 1 ;
obs | id | listing_1 | listing_2 | selling_1 | selling_2 | status | date_sign | la1_no_la1_5 |
1 | 1 | A | B | C | D | sold | 23-Jan-12 | 1 |
2 | 2 | C | D | sold | 1-May-07 | 0 | ||
3 | 3 | E | F | A | C | sold | 5-Mar-13 | 1 |
4 | 4 | B | A | expired | 15-Jan-11 | 0 | ||
5 | 5 | A | C | B | F | sold | 11-May-08 | 0 |
6 | 6 | E | D | sold | 15-Jul-11 | 0 | ||
7 | 7 | C | A | A | sold | 26-Nov-12 | 1 | |
8 | 8 | C | C | F | sold | 15-Dec-07 | 1 | |
9 | 9 | F | B | B | C | sold | 1-Dec-14 | 0 |
10 | 10 | D | A | expired | 14-Dec-11 | 0 |
It does not create new file
Add create table at the start of the query
We have no clue what "more complicated" conditions you want.
However this syntax is invalid.
past1.status=$EX
Do you mean
past1.status='$EX'
?
Yes, I want to count the number of transactions at hand. EX stands for expired and VE stands for sold.
Thank you
Freda
I don't understand what does past.1 stands for.
past1 is the name of a table.
There is only one table, but you merge it *with itself* on ID, and past1 only matches on past dates as per the match condition.
Here is the full data for the testing
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.