t**********r
发帖数: 182 | 1 My boss gave me this work in the morning and I had no idea. Please help. 奉
送20包子
two datafiles: one is all US blood centers information; and the other one is
all Canada blood centers information. The files are like this:
File 1: US blood centers
US_bloodcenterID; Date; US_AQuant; US_BQuant;
File 2: Canada blood centers
CAN_bloodcenterID; Date; CAN_AQuant; CAN_BQuant;
Purpose: for every US_bloodcenterID, select one CAN_bloodcenterID on the
SAME date to generate ONE file; but this one |
t**i
发帖数: 688 | 2 How many records per bloodcenterID?
One quick clue is to make cross-join first between US and CAN, then for each
US-CAN ID pair, find the single record with minimal difference between US_
BQuant and CAN_BQuant only. |
t**********r
发帖数: 182 | 3 Could you please write some code for me? use proc sql???
For each date yyyymmdd, there are thousands of bloodcentersID.
thanks. |
q**j
发帖数: 10612 | 4 very easy. maybe slow but it will work
proc sql;
create table output as
select a.*, b.*, diff = a.US_bloodcenterID - b.CAN_BQuant
from uscenter a, canadacenter b
where a.date = b.date
order by a.US_bloodcenterID, a.Date, diff;
quit;
data output;
set output;
by US_bloodcenterID USDate;
if first.USDate;
run;
50 baozi please! if you cannot sort by diff in proc sql, sort it in a separate proc sort step. |
t**********r
发帖数: 182 | 5 Many thanks!!!!
Let me run the code and BZ will be coming!!!!
【在 q**j 的大作中提到】
: very easy. maybe slow but it will work
: proc sql;
: create table output as
: select a.*, b.*, diff = a.US_bloodcenterID - b.CAN_BQuant
: from uscenter a, canadacenter b
: where a.date = b.date
: order by a.US_bloodcenterID, a.Date, diff;
: quit;
: data output;
: set output;
|
q**j
发帖数: 10612 | 6 i modified the code a little. but no major change. |
t**********r
发帖数: 182 | 7 Thank you so much.
A further question.
I have a third variable: US_CQuant and CAN_CQuant. When selecting from CAN_
BloodcenterID, I need also to make sure that the difference between US_
CQuant and CAN_CQuant is the minimum.
Can you also help me with this?
【在 q**j 的大作中提到】
: i modified the code a little. but no major change.
|
q**j
发帖数: 10612 | 8 see the modified code. here i assume diff1 is more important than diff2.
proc sql;
create table output as
select a.*, b.*, diff1 = a.US_BQuant - b.CAN_BQuant,
diff2 = a.US_CQuant - b.US_CQuant
from uscenter a, canadacenter b
where a.date = b.date
order by a.US_bloodcenterID, a.Date, diff1, diff2;
quit;
data output;
set output;
by US_bloodcenterID USDate;
if first.USDate;
run; |
t**********r
发帖数: 182 | 9 Thanks a million!!!! BZ has come to your a/c.
【在 q**j 的大作中提到】
: see the modified code. here i assume diff1 is more important than diff2.
: proc sql;
: create table output as
: select a.*, b.*, diff1 = a.US_BQuant - b.CAN_BQuant,
: diff2 = a.US_CQuant - b.US_CQuant
: from uscenter a, canadacenter b
: where a.date = b.date
: order by a.US_bloodcenterID, a.Date, diff1, diff2;
: quit;
: data output;
|
q**j
发帖数: 10612 | 10 多谢多谢。今天刚花了50个包子打听消息，没想到立刻就回本了。我的code肯定是不能
立刻就run的。但是问题很小很小。你稍微改改就行了。不行给我发message吧。俺滴
code是有warranty的。 |
