Join Now

Opbygge historik-eventdatabase

by Contributor SanderEhmsen ‎11-22-2016 07:41 AM - edited ‎11-22-2016 08:11 AM (286 Views)

Hej

 

Jeg har noget kontaktdata, som jeg skal have bygget en historiktabel op omkring.

Min drøm er at kunne opbygge en tabel a la: 

Navn       Telefonnummer     Firma      Stilling         Chef   Dato_start     Dato_slut

Sander         12345678         O.K.        Analytiker     Simon   01.05.2014   31.08.2016

Sander         12345678         TDC        Analytiker     Kim   01.09.2016   31.12.9999

 

Altså, hvis der ikke er nogen forskel fra den ene trækning til den næste, men hvis der kommer en ændring i fx chef, så skal der skrives en ny linje, der har start_dato lig=today(). Den tidligere række afsluttes med slut_dato=today()-1

 

For at gøre det lidt mere simpelt har jeg noget data her:

Det data, der er, ser i struktur ud som:

data hist;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 1 3
B B 20 1 3
;
run;

data aktuel;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 4 4
B A 25 4 4
;
run;

Her vil jeg gerne ende med data af denne struktur:

ID V1 V2 dato_start Dato_slut
A B 10 1 4
B B 20 1 3
B A 25 4 4

 

Jeg kan ikke lure, hvordan jeg skal skrive koden. Der er rigtig mange variable, der skal være ens. Men jeg kan godt taste dem enkeltvis i et if-statement, hvis det er det, der skal til. 

 

Jeg selv tænker noget i stil med 

data saml;
set hist aktuel;
run;
proc sort data=saml;
by id dato_slut ;;
run;

data data1;
set saml;
by id;
if first.ID then n=1;
else n=2;
run;

data data2;
set data1;
retain dato_start;
hest=0;
if n=2 then do;
	if v1=lag(v1) and v2=lag(v2) dato_start=lag(dato_start) ;

	end;
run;

Men jeg kan ikke få det til at virke. Og det virker også som en utrolig klodset løsning...

 

Er der nogen, der har gode råd eller måske endda en løsning liggende?

 

 

Comments
by Contributor SanderEhmsen
on ‎11-23-2016 05:11 AM

Jeg fik en idé, som jeg har prøvet at gøre, me ndet er utrolig uelegant. Så hvis nogen har noget kode liggende, der er elegant, må de meget gerne dele.

 

data hist;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 1 3
B B 20 1 3
;
run;

data aktuel;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 4 4
B A 25 4 4
;
run;

 

data saml;
set hist aktuel;
run;
proc sort data=saml;
by id dato_slut ;;
run;

data hist2 (drop= dato_start dato_slut);
set hist;
run;

data aktuel2 (drop= dato_start dato_slut);
set aktuel;
run;

proc compare base=hist2 compare=aktuel2 out=test ;
id id;
run;

proc sql;
create table saml2 as select
t1.*, case when t2.v1="........" and t2.v2=0 then 1 else . end as samme_vaerdier,
case when t2.v1="........" and t2.v2=0 then max(dato_slut) else dato_slut end as dato_slut2
from saml as t1 left join test t2 on t1.id=t2.id
group by t1.id;
quit;

data saml3 (drop=samme_vaerdier dato_slut2);
set saml2;
if samme_vaerdier=1 and dato_slut=dato_slut2 then delete;
if samme_vaerdier=1 then dato_slut=dato_slut2;
if samme_vaerdier=. and dato_start=dato_slut then delete;
run;

proc sql noprint;
select cats("'",ID,"'") into :id separated by ', '
from saml3
where (dato_slut=4);
run;

data aktuel2;
set aktuel(where=(id not in (&id)));
run;

data master;
set saml3 aktuel2;
run;
by SAS Employee ChristianKjeldsen
on ‎12-01-2016 06:57 AM

Hej Sander

 

Jeg ved ikke, om denne løsning er mere elegant, men den benytter i hvert fald SQL til dette. 

 

Bemærk, at jeg har taget udgangspunkt i en lettere opdateret version af dine input data. Jeg er desuden ikke helt sikker på, hvordan du i praksis har din Dato_slut variabel, for i dit forretningseksempel ser det ud til, at der er en Dummy dato for den aktuelle række (31.12.9999), mens i dit dataeksempel er det den nuværende dato (4). Jeg har lavet koden med udgangspunkt i det sidste.

 

data hist;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 15 1 2
A B 10 3 3
B B 20 1 3
;
run;

data aktuel;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 4 4
B A 25 4 4
C A 25 4 4
;
run;

%let dag=4;
proc sql;
	/* Tabel med ID'er fra aktuel, der matcher med historisk */
	create table match as
	select distinct ID from
		(
		select ID, V1, V2 from aktuel
		intersect all
		select ID, V1, V2 from hist where Dato_slut=&dag-1
		)
	;
	/* Tabel med ID'er fra aktuel, som er nye */
	create table ny as
	select distinct ID from
		(
		select ID, V1, V2 from aktuel
		except all
		select ID, V1, V2 from hist where Dato_slut=&dag-1
		)
	;
	create table hist_ny (drop=Dato_slut_old)as
		/* Data i historik tabel, som er historiske */
		select *
		from hist 
		where (
			Dato_slut<&dag-1 	/* Slut dato tidligere */
			or
			ID not in           /* ID ikke længere aktuel */
				(select distinct ID from aktuel)
			or
			ID in 				/* Ny række for ID */
				(select distinct ID from ny)
			)
		outer union corr
		/* Data i historik tabel som skal viderføres */
		select *, &dag as Dato_slut 
		from hist (rename=(Dato_slut=Dato_slut_old))
		where (
			hist.Dato_slut_old=&dag-1 
			and 
			id in 
				(select ID from match)
			)
		outer union corr
		/* Data i aktuel tabel som skal tilføjes */
		select * 
		from aktuel
		where ID in
			(select ID from ny)
	order by ID, Dato_slut
	;
quit;
	

Men så er der i hvert fald lidt inspiration

by SAS Employee ChristianKjeldsen
on ‎12-01-2016 07:34 AM

Og nu fik jeg også lavet et eksempel, hvor der benyttes en dummy dato (999) til at afgøre, hvad der er sidste dato. I mit eksempel har jeg antaget, at ID'er som ikke findes i aktuel skal stoppes. 

 

data hist;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 15 1 2
A B 10 3 999
B B 20 1 999
;
run;

data aktuel;
input ID $ V1 $ V2 dato_start Dato_slut ;
datalines;
A B 10 4 999
B A 25 4 999
C A 25 4 999
;
run;

%let dag=4;
%let dummy=999;
proc sql;
	/* Tabel med ID'er fra aktuel, der matcher med historisk */
	create table match as
	select distinct ID from
		(
		select ID, V1, V2 from aktuel
		intersect all
		select ID, V1, V2 from hist where Dato_slut=&dummy
		)
	;
	/* Tabel med ID'er fra aktuel, som er nye */
	create table ny as
	select distinct ID from
		(
		select ID, V1, V2 from aktuel
		except all
		select ID, V1, V2 from hist where Dato_slut=&dummy
		)
	;
	create table hist_ny (drop=Dato_slut_old) as
		/* Data i historik tabel, som videreføres */
		select *
		from hist 
		where (
			Dato_slut ne &dummy 	/* Slut dato tidligere */
			or
			ID in 				/* Række videreføres */
				(select distinct ID from match)
			)
		outer union corr
		/* Data i historik tabel som stoppes */
		select *, &dag-1 as Dato_slut 
		from hist (rename=(Dato_slut=Dato_slut_old))
		where (
			hist.Dato_slut_old=&dummy 
			and
			( 
				id in 				/* Ny række */
					(select ID from ny)
				or
				ID not in           /* ID ikke længere aktuel */
					(select distinct ID from aktuel)
				)
			)
		outer union corr
		/* Data i aktuel tabel som skal tilføjes */
		select * 
		from aktuel
		where ID in
			(select ID from ny)
	order by ID, Dato_slut
	;
quit;
	
by New Contributor Widen
on ‎01-06-2017 07:05 AM

Hej Sander

Jeg har haft et lignende problem, på en tabel med ca. 70 mio. rækker så jeg også var nødt til at kigge på performance - den løsning jeg kom frem til som den klart bedste rent performance mæssigt var følgende:

 

data hist;

input ID $ V1 $ V2 dato_start Dato_slut ;

datalines;

A B 15 1 2

A B 10 3 999

B B 20 1 999

;

run;

data aktuel;

input ID $ V1 $ V2 dato_start Dato_slut ;

datalines;

A B 10 4 999

B A 25 4 999

C A 25 4 999

;

run;

 

Data NY_HIST (drop=old_start);

Set hist (rename=(dato_start=old_start) in=H) aktuel (in=A);

If _N_=1 then do;

Declare hash H1(dataset:"aktuel");

H1.definekey("ID");

H1.definedata('Dato_start');

H1.definedone();

End;

If H then do;

if Dato_slut=999 then do;

If H1.find()=0 then do;

Dato_slut=Dato_start-1;

End;

End;

Dato_start=old_start;

End;

Run;

 

Jeg har modsat Christian antaget at folk der ikke findes i aktuel, skal fortsætte uændret (dvs. have slutdato 31/12 9999)

by Contributor SanderEhmsen
on ‎01-06-2017 07:12 AM

Hej Wilden

 

Tak for dit svar. Og i øvrigt tak til Christian også. Jeg følte mig overbevist om, at jeg havde skrevet en besked herinde, men fik måske aldrig trykket på 'Post'.

 

Jeg får kigget din igennem ved lejlighed, Wilden. Jeg har aldrig rigtig forstået Hash-objekter, men det er selvfølgelig endnu en lejlighed til at tage mig sammen til det nu.

 

Vh. Sander. 

by New Contributor john88
on ‎04-01-2017 02:56 AM