topic Re: Selecting unique values from a specific event date in SAS Programming

Selecting unique values from a specific event date

mtee15 — Mon, 14 Mar 2022 16:25:57 GMT

This is my table I would like to create and output table that contains only the unique contact types and must contain the denom value of 1.

ID_KEY	EVENT_KEY	SERVICE_DATE	TYPE_OF_CONTACT	denom
100	50068022	03Jan2017	By phone	1
100	50068024	03Jan2017	By phone	.
100	50068029	03Jan2017	By phone	.
100	50068023	03Jan2017	In person - individual	.
100	50068026	03Jan2017	In person - individual	.
100	50068025	03Jan2017	In person - individual	.
100	50068021	03Jan2017	In person - individual	.
100	50068011	03Jan2017	In person - individual	.
100	50068067	03Jan2017	By email	.

The final table would look like this:

ID_KEY	EVENT_KEY	SERVICE_DATE	CONTACT_TYPE	denom
100	50068022	03Jan2017	By phone	1
100	50068023	03Jan2017	In person - individual	.
100	50068067	03Jan2017	By email	.

Re: Selecting unique values from a specific event date

ballardw — Mon, 14 Mar 2022 16:44:57 GMT

Are there more than one value of ID_Key? If so is this process supposed to provide a similar unique value for each ID_key?

Is the data in order such that all the type_of_contact values are grouped as shown or will they be mixed?

Is your "denom" always in the first record of the Id_key as shown?

Re: Selecting unique values from a specific event date

Ksharp — Tue, 15 Mar 2022 12:40:01 GMT

If I understood what you mean .

data have;
input ID_KEY /
EVENT_KEY  $40. /
SERVICE_DATE  date9. /
TYPE_OF_CONTACT $40. /
denom
;
format SERVICE_DATE  date9.;
cards;
100
50068022
03Jan2017
By phone
1
100
50068024
03Jan2017
By phone
.
100
50068029
03Jan2017
By phone
.
100
50068023
03Jan2017
In person - individual
.
100
50068026
03Jan2017
In person - individual
.
100
50068025
03Jan2017
In person - individual
.
100
50068021
03Jan2017
In person - individual
.
100
50068011
03Jan2017
In person - individual
.
100
50068067
03Jan2017
By email
.
;

proc sql;
create table temp as
select  *
 from have
  where catx('|',ID_KEY,SERVICE_DATE) in
   (select catx('|',ID_KEY,SERVICE_DATE) from have where denom=1);
quit;
data want;
 set temp;
 by ID_KEY TYPE_OF_CONTACT notsorted;
 if first.TYPE_OF_CONTACT;
run;