Prev: Data step Question
Next: is this possible with proc kde
From: Metricsdude on 17 Mar 2010 18:29
Hi,

I have two datasets one containing visits details and one holding
asset details and i would like to update the visit details dataset
with asset that was at the property on the date of the visit:

Visit dataset example

ref visit type Visit Date
123 install 01/04/2009

asset dataset example

ref asset type effective from effective to
123 N 13/03/2007 21/09/2008
123 K 21/09/2009 01/04/2009
123 S 01/04/2009

In the example above i would like to return:

ref visit type Visit Date asset type
123 install 01/04/2009 K


I can merge on using the ref but not sure how i restrict the results
to show me the one line with the correct meter details

Any help would be appreciated.

Cheers
Ben
From: Patrick on 18 Mar 2010 05:24
Hi Ben

Looks to me as if a PROC SQL, UPDATE with a sub select and a where
clause could do the job.

An alternative would be to use the SAS Update or Modify statement.

HTH
Patrick
From: Sri on 18 Mar 2010 14:36
On Mar 17, 6:29 pm, Metricsdude <bsj_ad...(a)hotmail.com> wrote:
> Hi,
>
> I have two datasets one containing visits details and one holding
> asset details and i would like to update the visit details dataset
> with asset that was at the property on the date of the visit:
>
> Visit dataset example
>
> ref     visit type     Visit Date
> 123       install          01/04/2009
>
> asset dataset example
>
> ref         asset type   effective from         effective to
> 123           N                   13/03/2007       21/09/2008
> 123           K                   21/09/2009       01/04/2009
> 123           S                   01/04/2009
>
> In the example above i would like to return:
>
> ref     visit type     Visit Date              asset type
> 123       install          01/04/2009              K
>
> I can merge on using the ref but not sure how i restrict the results
> to show me the one line with the correct meter details
>
> Any help would be appreciated.
>
> Cheers
> Ben

Hi Ben,
According to the example you provided, you can first sort and get rid
of any unwanted rows and then merge. Here is how i did it.

data visit;
input ref $ visittype $ visit_date mmddyy10.;
cards;
123 install 01/04/2009
;

data asset;
input ref $ asset_type $ effective_from ddmmyy10. +1 effective_to
ddmmyy10.;
cards;
123 N 13/03/2007 21/09/2008
123 K 21/09/2009 01/04/2009
123 S 01/04/2009
;

proc sort data=visit;
by ref visit_date;
run;

proc sort data=asset;
by ref descending effective_to;
run;

data asset1;
set asset;
by ref descending effective_to;
if first.ref;
run;

data final;
merge visit asset1;
by ref;
format visit_date effective_to effective_from date9.;
run;

Hope this helps you.

Sri
From: Ben Adams on 19 Mar 2010 05:05
On 18 Mar, 18:36, Sri <subhadra...(a)gmail.com> wrote:
> On Mar 17, 6:29 pm, Metricsdude <bsj_ad...(a)hotmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I have two datasets one containing visits details and one holding
> > asset details and i would like to update the visit details dataset
> > with asset that was at the property on the date of the visit:
>
> > Visit dataset example
>
> > ref     visit type     Visit Date
> > 123       install          01/04/2009
>
> > asset dataset example
>
> > ref         asset type   effective from         effective to
> > 123           N                   13/03/2007       21/09/2008
> > 123           K                   21/09/2009       01/04/2009
> > 123           S                   01/04/2009
>
> > In the example above i would like to return:
>
> > ref     visit type     Visit Date              asset type
> > 123       install          01/04/2009              K
>
> > I can merge on using the ref but not sure how i restrict the results
> > to show me the one line with the correct meter details
>
> > Any help would be appreciated.
>
> > Cheers
> > Ben
>
> Hi Ben,
> According to the example you provided, you can first sort and get rid
> of any unwanted rows and then merge. Here is how i did it.
>
> data visit;
> input ref $ visittype $ visit_date mmddyy10.;
> cards;
> 123 install 01/04/2009
> ;
>
> data asset;
> input ref $ asset_type $ effective_from ddmmyy10. +1  effective_to
> ddmmyy10.;
> cards;
> 123 N 13/03/2007 21/09/2008
> 123 K 21/09/2009 01/04/2009
> 123 S 01/04/2009
> ;
>
> proc sort data=visit;
> by ref visit_date;
> run;
>
> proc sort data=asset;
> by ref descending effective_to;
> run;
>
> data asset1;
>  set asset;
> by ref descending effective_to;
> if first.ref;
> run;
>
> data final;
>  merge visit asset1;
> by ref;
> format visit_date effective_to effective_from date9.;
> run;
>
> Hope this helps you.
>
> Sri- Hide quoted text -
>
> - Show quoted text -

Hi all

Thanks for the advice, i don't think i can use the sort method as one
ref might have multiple visits...

I have used a proc update query but it seems to be running for an
extremley long time (over 15 hours now) there are approx 1.8 million
visits so it is a large dataset but this run time seems to indicate an
issue with the sql???.

proc sql;
update lib.jobdata1 set product= (select product_id from lib.asset
where visit.ref= asset.ref
and
EFFECTIVE_FROM_DATE < datepart(visit_date) and (EFFECTIVE_TO_DATE =
datepart(visit_date) or EFFECTIVE_TO_DATE =.));
quit;
 | 
Pages: 1
Prev: Data step Question
Next: is this possible with proc kde