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 a | s*r
发帖数: 2757 | 2 you have only 4
【在 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 | | t**********r
发帖数: 182 | 4 Has figured it out. Thanks.
===============
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 columns.
587 quit;
NOTE: PROCEDURE SQL used (Tota | y******0
发帖数: 401 | 5 proc sql;
create table data3 as
select data1.var1,data1.var2, data2.rating, min(date1-date2) as diff
from data1, data2
where data1.var1=data2.var1
and data1.var2=data2.var2
and date1>date2
group by 1,2,3;
quit; | m*********n
发帖数: 413 | 6 your code is wrong
functions could not be using in the HAVING subclasue.
【在 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.
|
|
