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 | . |
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;
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?
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.