t**********r
发帖数: 182 | 1 Want to merge two data sets using proc sql:
Data1:
var1 var2 date1
Data2:
var1 var2 date2 rating
(Note: var1 and var2 are the same variables in these two data sets)
Question:
Select rating in data2 to data1; meeting the following criteria:
1. date1 - date2 >0
2. date1 - date2 has the minimum value.
I wrote the following code; but it won't work:
proc sql;
create table data3 as
select data1.*, data2.rating, date1-date1 as diff
from data1, data2
where data1.var1=data2.var1 and data1.var2=data2.var2 |
m********5
发帖数: 619 | 2 你这个条件1和条件2我看着怎么重叠啊....
proc sql; create table data3 as select a.*, b.* from data1 as a, data2 as b
where a.var1=b.var1 and a.var2=b.var2 and a.date1-b.date2>=minvalue and a.
date1-b.date2>0;
quit;
【在 t**********r 的大作中提到】
: Want to merge two data sets using proc sql:
: Data1:
: var1 var2 date1
: Data2:
: var1 var2 date2 rating
: (Note: var1 and var2 are the same variables in these two data sets)
: Question:
: Select rating in data2 to data1; meeting the following criteria:
: 1. date1 - date2 >0
: 2. date1 - date2 has the minimum value.
|
t**********r
发帖数: 182 | 3 Thank you.
你这个条件1和条件2我看着怎么重叠啊....
No. There are many obs that meet date1 - date2 >0; but I only want the one
with the min value. |
S****Y
发帖数: 4634 | 4 post log
【在 t**********r 的大作中提到】
: Want to merge two data sets using proc sql:
: Data1:
: var1 var2 date1
: Data2:
: var1 var2 date2 rating
: (Note: var1 and var2 are the same variables in these two data sets)
: Question:
: Select rating in data2 to data1; meeting the following criteria:
: 1. date1 - date2 >0
: 2. date1 - date2 has the minimum value.
|
m********5
发帖数: 619 | 5 如果你这个min value>0
>0的条件就是多余的
如果你这个min value<0
>min value这个条件就是多余的。。。
【在 t**********r 的大作中提到】
: Thank you.
: 你这个条件1和条件2我看着怎么重叠啊....
: No. There are many obs that meet date1 - date2 >0; but I only want the one
: with the min value.
|
t**********r
发帖数: 182 | 6 I tried your code - it does not work... |
m********5
发帖数: 619 | 7 hehe, good luck finding one that works
【在 t**********r 的大作中提到】
: I tried your code - it does not work...
|
t**********r
发帖数: 182 | 8 This min value is changing for different observations..
【在 m********5 的大作中提到】
: 如果你这个min value>0
: >0的条件就是多余的
: 如果你这个min value<0
: >min value这个条件就是多余的。。。
|
m********5
发帖数: 619 | 9 你以为proc sql是机器人啊
你既然有dataset 3 containing a variable (minvalue)
你不告诉sas，sas怎么算
【在 t**********r 的大作中提到】
: This min value is changing for different observations..
|
t**********r
发帖数: 182 | 10 ERROR: The following columns were not found in the contributing tables: diff.
516 having diff=min(diff);
------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
【在 S****Y 的大作中提到】
: post log
|
S****Y
发帖数: 4634 | 11 先把having前面的";"删了
diff.
【在 t**********r 的大作中提到】
: ERROR: The following columns were not found in the contributing tables: diff.
: 516 having diff=min(diff);
: ------
: 180
: ERROR 180-322: Statement is not valid or it is used out of proper order.
|
t**********r
发帖数: 182 | 12 Many thanks for your hint!! I made it. Here is the code.
579 proc sql;
580 create table data3 as
581 select data1.*, data2.rating, day1-day2 as diff
582 from data1, data2
583 where data1.var1=data2.var1 and data1.var2=data2.var2
584 and date1-date2>0
585 group by data1.var1, data1.var2, data1.date1
586 having diff=min(diff);
NOTE: The query requires remerging summary statistics back with the original
data.
NOTE: Table WORK.data3 created, with 48144 rows and 9 |
t**********r
发帖数: 182 | 13 10 baozi has been sent. Thanks.
【在 m********5 的大作中提到】
: 你以为proc sql是机器人啊
: 你既然有dataset 3 containing a variable (minvalue)
: 你不告诉sas，sas怎么算
|
t**********r
发帖数: 182 | 14 10 baozi has been sent. thanks.
【在 S****Y 的大作中提到】
: 先把having前面的";"删了
:
: diff.
|
