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 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.
Please see the attachments for the data.
Thank you
Freda
First of all, your join will leave no results, as you are testing for statuses beginning with a $ sign, but no such statuses are present.
Second, you best start to check your conditions manually against the data. Even with the literals stripped of the dollar signs, I got only 2 matches for the whole dataset, for id=2.
eg id=1 is an observation with status='EX', so it would need a match
- where the other (past1) date_sign is between date_sign and date_exp (not present)
- where the other status is an 'EX' and the other date_ep falls between data_sign and date_exp (also not present)
- where the other status is a 'VE' and the other date_sold falls between date_sign and date_exp (also not present)
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
Please provide the data step with which you read the csv.
@freda wrote:
Hi,
I did not import this data use data step. I used proc import:
proc import datafile = 'C:\Users\Samuel\Desktop\testing.csv'
out = la1
dbms = csv
replace;
run;
Thank you
You do not know it yet, but you used a data step. You find the code in the log of the proc import.
First of all, your join will leave no results, as you are testing for statuses beginning with a $ sign, but no such statuses are present.
Second, you best start to check your conditions manually against the data. Even with the literals stripped of the dollar signs, I got only 2 matches for the whole dataset, for id=2.
eg id=1 is an observation with status='EX', so it would need a match
- where the other (past1) date_sign is between date_sign and date_exp (not present)
- where the other status is an 'EX' and the other date_ep falls between data_sign and date_exp (also not present)
- where the other status is a 'VE' and the other date_sold falls between date_sign and date_exp (also not present)
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.