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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.