Skip to main content

Recency, Frequency, Monetary (RTF) variable generation using Danish National Health Register data.

Description

Below adaptable SAS code for generation of recency, frequency and monetary variables.
Algorithm may be applied in a lot of contexts in which records contain dates ('day of entry', 'day of exit', 'day of visit') id number and maybe even revenue or cost.

Citation

Please cite this code as:
Laier, G.H. (2016) Recency, Frequency and Monetary SAS programming script  [computer software]. Denmark. Link: http://hellmund.blogspot.dk/2016/02/recency-frequency-monetary-rtf-variable.html

Thanks!
Gunnar Hellmund Laier,
PhD, MSc

Explanation

In this context we form variable for analyses of Danish National Register data and form variables containing information on contacts, hospitalizations and days in hospital 14, 30, 91 and 180 days before a hospitalization or contact.

Key variables:
cpr (security number), pattype (patient type, in- or outpatient), inddto (day of entry), uddto (day of exit), ambdto (day of visit).

Data step program

data calc.RFMdata(drop=dto_hist ind_hist pat_hist ind k pdt pat i inddto_ uddto_);
set stalist;
retain dto_hist ind_hist pat_hist;
by cpr;


prev=.;
length dto_hist $4000;
length ind_hist $4000;
length pat_hist $4000;

timedif=0;
*Number of hospitalizations within last 14 days;
tnum14=0;
*Number of hospitalizations within last 30 days;
tnum30=0;
*Number of hospitalizations within last 91 days;
tnum91=0;
*Number of hospitalizations within last 180 days;
tnum180=0;


*Inpatient hospitalizations (contacts/days in bed);
num14=0;
seng14=0;
*Within last 30 days;
num30=0;
seng30=0;
*Within last 91 days;
num91=0;
seng91=0;
*Within last 180 days;
num180=0;
seng180=0;

uddto_=uddto;
inddto_=inddto;
format inddto_;
format uddto_;

if pattype EQ 2 then do;
uddto_=ambdto;
inddto_=ambdto;
sengdage=0;
end;

if missing(uddto_) then do;
if (pattype in (0 1)) AND not(missing(sengdage)) then uddto_=inddto_+sengdage;
end;

if first.cpr then do;
dto_hist=strip(put(uddto_,8.));
ind_hist=strip(put(inddto_,8.));
pat_hist=strip(put(pattype,8.));
end;
else do;

 k=count(strip(dto_hist),';');
 do i = 1 to k+1;

  pdt=input(scan(dto_hist,i,';'),8.);
  ind=input(scan(ind_hist,i,';'),8.);
  pat=input(scan(pat_hist,i,';'),8.);

  if pdt LT inddto_ then do;

  if missing(prev) then prev=pdt;

  timedif=inddto_-pdt;
  if missing(pdt) then do;
  put"ERROR Missing pdt value, obs number ";
  put _n_;
  end;

  if (timedif LE 180) AND (timedif GT 91) then do;
    tnum180=tnum180+1;
if pat in (0 1) then do;
num180=num180+1;
seng180=seng180+pdt-max(ind,inddto_-180);
end;
  end;
  if (timedif LE 91) AND (timedif GT 30) then do;
    tnum180=tnum180+1;
    tnum91=tnum91+1;
if pat in (0 1) then do;
num91=num91+1;
num180=num180+1;
seng91=seng91+pdt-max(ind,inddto_-91);
seng180=seng180+pdt-max(ind,inddto_-180);
end;
  end;
  if (timedif LE 30) AND (timedif GT 14) then do;
    tnum180=tnum180+1;
    tnum91=tnum91+1;
tnum30=tnum30+1;
if pat in (0 1) then do;
num30=num30+1;
num91=num91+1;
num180=num180+1;
seng30=seng30+pdt-max(ind,inddto_-30);
seng91=seng91+pdt-max(ind,inddto_-91);
seng180=seng180+pdt-max(ind,inddto_-180);
end;
  end;
  if timedif LE 14 then do;
  tnum14=tnum14+1;
  tnum30=tnum30+1;
  tnum91=tnum91+1;
  tnum180=tnum180+1;
if pat in (0 1) then do;
num14=num14+1;
num30=num30+1;
num91=num91+1;
num180=num180+1;
seng14=seng14+pdt-max(ind,inddto_-14);
seng30=seng30+pdt-max(ind,inddto_-30);
seng91=seng91+pdt-max(ind,inddto_-91);
seng180=seng180+pdt-max(ind,inddto_-180);
end;
  end;
  end;

  end;

dto_hist=strip(put(uddto_,8.))||';'||strip(dto_hist);
ind_hist=strip(put(inddto_,8.))||';'||strip(ind_hist);
pat_hist=strip(put(pattype,8.))||';'||strip(pat_hist);
end;

run;

Comments

Popular posts from this blog

Alder/korrekt århundrede udfra cpr nummer

De fleste, der arbejder med registre eller databaser, står ofte med problemstillingen, at alder er uoplyst, medens cpr-nummer er kendt. Hvordan regner man den ud? Følgende regel er gældende: Hvis syvende ciffer er 0, 1, 2 eller 3 er man født i det 20. århunderede (1900-tallet) Ligeledes, hvis syvende ciffer er 4 eller 9, og årstallet (femte og sjette ciffer) er større end eller lig 37. Endelig er man født i det 19. århundrede (1800-tallet) hvis syvende ciffer er 5, 6, 7 eller 8 og årstallet er større end eller lig 58. Nedenfor finder du eksempel i SAS kode: En lille makro, der udover fødselsdato også udregner køn samt den præcise alder givet datovariabel. Kilde: Opbygning af CPR nummeret, cpr.dk proc format library=work; value gender 0="Female" 1="Male" ; run; %macro agefromCPR(cpr,datevar=inddto,birthvar=birth,agevar=age); dy_temp=input(substrn(&cpr,1,2),2.); mt_temp=input(substrn(&cpr,3,2),2.); yr_temp=input(substrn(&cpr,5,2),

HackRF on Windows 8

This technical note is based on  an extract from thread . I have made several changes and added recommendations. I have experienced lot of latency using GnuRadio and HackRF on Pentoo Linux, so I wanted to try out GnuRadio on Windows. HackRF One is a transceiver, so besides SDR capabilities, it can also transmit signals, inkluding sweeping a given range, uniform and Gaussian signals. Pentoo Linux provides the most direct access to HackRF and toolboxes. Install Pentoo Linux on a separate drive, then you can use osmocom_siggen from a terminal to transmit signals such as near-field GSM bursts, which will only be detectable within a meter. Installation of MGWin and cmake: Download and install the following packages: - MinGW Setup (Go to the Installer directory and download setup file) - CMake (I am using CMake 3.2.2 and I installed it in C:\CMake, this path is important in the commands we must send in the MinGW shell) Download and extract the packages

Comorbidity indexes in SQL

Generating Elixhauser comorbidity index from Danish National Health Register as relational database. ( ICD 10 Coding  in SAS) A lookup-table based version of Charlson comorbidity index I made in SQL. A similar approach can be applied to Elixhauser. SELECT V_CPR, MAX(EI1)+MAX(EI2)+MAX(EI3)+MAX(EI4)+MAX(EI5)+ MAX(EI6)+MAX(EI7)+MAX(EI8)+MAX(EI9)+MAX(EI10)+ MAX(EI11)+MAX(EI12)+MAX(EI13)+MAX(EI14)+MAX(EI15)+ MAX(EI16)+MAX(EI17)+MAX(EI18)+MAX(EI19)+MAX(EI20)+ MAX(EI21)+MAX(EI22)+MAX(EI23)+MAX(EI24)+MAX(EI25)+ MAX(EI26)+MAX(EI27)+MAX(EI28)+MAX(EI29)+MAX(EI30)+MAX(EI31) AS Elixhauser FROM (SELECT V_CPR, -- Congestive Heart Failure CASE WHEN DIAG LIKE 'DI099%' OR DIAG LIKE 'DI110%' OR DIAG LIKE 'DI130%' OR DIAG LIKE 'DI132%' OR DIAG LIKE 'DI255%' OR DIAG LIKE 'DI420%' OR DIAG LIKE 'DI425%' OR DIAG LIKE 'DI426%' OR DIAG LIKE 'DI427%' OR DIAG LIKE 'DI428%' OR DIAG LIKE 'DI429%' OR D