topic Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions in SAS Programming

how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Fri, 03 Aug 2018 02:54:03 GMT

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

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

PeterClemmensen — Fri, 03 Aug 2018 04:39:03 GMT

When you say "within five years", does that mean looking back five years?

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Fri, 03 Aug 2018 04:40:24 GMT

Yes, within last 5 years of the date_sign.

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

ChrisNZ — Fri, 03 Aug 2018 05:00:50 GMT

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

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Fri, 03 Aug 2018 05:13:40 GMT

Thank you so much. I copied your code, it generates the desired result in the output. However, it does not create new file by telling me the following error message:
NOTE: The query requires remerging summary statistics back with the original data.
Do you know how to solve this issue?
Thank you again.

Freda

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

ChrisNZ — Fri, 03 Aug 2018 05:16:05 GMT

It does not create new file

Add create table at the start of the query

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Fri, 03 Aug 2018 05:21:22 GMT

Thank you so much. It works.

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Thu, 09 Aug 2018 05:44:38 GMT

Hello,

May I ask if I want to add the more complicated condition on for the count if, how should I modify the code? I tried the following but failed.
proc sql;
create table table1 as
select unique a.*, count(past1.date_sign) as la1_no_hand_la1
from lal
left join
lal past1
on (lal.listing_1=past1.listing_1 | lal.listing_1=past1_listing_2 | lal.listing_1=past1_listing_3 | lal.listing_1=past1_listing_4)
& ((lal.date_sign< past1.date_sign < lal.date_exp & past1.status=$EX) | (lal.date_sign< past1.date_sign < lal.date_sold & past1.status=$VE)|(lal.date_sign< past1.date_exp < lal.date_exp & past1.status=$EX & lal.status=$EX)|(lal.date_sign< past1.date_exp < lal.date_sold & past1.status=$EX & lal.status=$VE) | (lal.date_sign< past1.date_sold < lal.date_exp & past1.status=$VE & lal.status=$EX)|(lal.date_sign< past1.date_sold < lal.date_sold & past1.status=$VE & lal.status=$VE) )
group by 1
order by 1 ;

Could you give me some suggestion?

Thank you so much

Freda

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

ChrisNZ — Thu, 09 Aug 2018 06:07:27 GMT

We have no clue what "more complicated" conditions you want.

However this syntax is invalid.

past1.status=$EX

Do you mean

past1.status='$EX'

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Thu, 09 Aug 2018 15:10:45 GMT

Yes, I want to count the number of transactions at hand. EX stands for expired and VE stands for sold.

Thank you

Freda

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Thu, 09 Aug 2018 21:01:14 GMT

Hello,

I modified the code as follows:

proc sql;
create table table1 as
select unique la1.*, count(?) as la1_no_hand_la1
from la1
left join
la1 past1
on (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_sign < la1.date_exp and la1.status="$EX" )
or (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_sign < la1.date_sold and la1.status="$VE")
or (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_exp < la1.date_exp and past1.status="$EX" and la1.status="$EX")
or (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_exp < la1.date_sold and past1.status="$EX" and la1.status="$VE")
or (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_sold < la1.date_exp and past1.status="$VE" and la1.status="$EX")
or (la1.listing_1=past1.listing_1 and la1.date_sign< past1.date_sold < la1.date_sold and past1.status="$VE" and la1.status="$VE")
group by 1
order by 1 ;

However, I don't know what I should put instead of the ? in the count(?) because I don't understand what does past.1 stands for. I tried the original code provided by you as count(past1.date_sign), but it does not work this time.
The result I want to have is the listing at hand during the contract period. For example, for id=1, the listing_1 A has its own contract period between date_sign and date_exp or date_sold if the listing has been sold. I want to count all the listings the first listing agent A involved.

Thank you

Freda

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Thu, 09 Aug 2018 21:11:01 GMT

The result supposed to be:
id result
1 1
2 5
3 0
4 1
5 0
6 2
7 0
8 0
9 0
10 1
11 1
12 0
13 2
14 0
15 0
16 0
17 0
18 1
19 0
20 1
21 0
22 0
23 0
24 1
25 1
26 0
27 0
28 1
29 1
30 0

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

freda — Thu, 09 Aug 2018 21:12:00 GMT

Here is the full data for the testing

Thank you

Re: how to transfer the stata code to sas code: a loop to count the no. of obs under conditions

ChrisNZ — Fri, 10 Aug 2018 04:17:12 GMT

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.