topic Re: SAS query in SAS Programming

SAS query

scb — Tue, 20 Dec 2016 11:19:21 GMT

DATASET A

CASE DATE CUST STATUS

A1 01JAN1990 123 NEW

A2 02JAN1990 290 NEW

DATASET B

CASE DATE CUST STATUS

A3 01FEB1990 380 NEW

A4 02FEB1990 490 NEW

A5 02FEB1990 290 OLD

A6 03FEB1990 490 OLD

There are 2 monthly datasets.The customer status was determined by the DATE. If the CUST appears first time then categorized as NEW customer.

If the customer appears in the same month, the first record will be categorized as NEW and subsequent will be as OLD.

Anyone can help? Thanks.

Re: SAS query

LinusH — Tue, 20 Dec 2016 11:35:19 GMT

You need to append the data sets. Then sort and use BY/first. logic.

Re: SAS query

PoojaP1 — Tue, 20 Dec 2016 12:28:26 GMT

You need to check the observations by using BY statement/cust.first and date.first (to check if that particular customer appeared first time on that date).

Re: SAS query

Kurt_Bremser — Tue, 20 Dec 2016 12:42:53 GMT

Customer 290 does not meet your rule for "OLD" in observation 3 of dataset B. It is the first appearance in the month of February.

Quote:

"If the customer appears in the same month, the first record will be categorized as NEW and subsequent will be as OLD."

Re: SAS query

Ksharp — Wed, 21 Dec 2016 11:03:11 GMT

What does DATASET A stands for ? Does DATASET A contain cust which all are status=new /

data A;
input (CASE    DATE                  CUST    STATUS) (:$20.);
cards;
A1        01JAN1990        123       NEW
A2        02JAN1990        290       NEW
;
data B;
input (CASE    DATE                  CUST   ) (:$20.);
cards;
A3        01FEB1990         380    
A4        02FEB1990         490         
A5        02FEB1990         290    
A6        03FEB1990         490  
;
run;

data want;
 if _n_=1 then do;
  if 0 then set a;
  declare hash h(dataset:'a');
  h.definekey(key:'cust');
  h.definedone();
 end;
set b;
if h.check()=0 then status='old';
 else do;status='new';h.add();end;
run;