topic Re: Missing Data Elements? in SAS Programming

Missing Data Elements?

IgawaKei29 — Fri, 11 Oct 2019 12:05:20 GMT

Good Morning All. I have never come across something like this issue before so I am not sure how to search for the issue. I am not sure what something like this would be called. I appreciate any guidance on this.

So the issue is I have data that has an ID number and for each ID number there should be 7 keys associated. See example below.

ID KEY

00001 01

00001 02

00001 03

00001 04

00001 05

00001 06

00001 07

However some data is coming through like this

ID KEY

00001 01

00001 02

00001 04

00001 07

How can I identify within SAS IDs that do not have the full 7 keys associated?

Again thank you for any help or guidance. I did have the idea of adding a row number count, so I will be trying that.

Re: Missing Data Elements?

PaigeMiller — Fri, 11 Oct 2019 12:06:37 GMT

Create a data set which you know has all 7 keys for each ID. Then compare this created data set to the actual data set to see what isn't present in the actual data set.

/* Create fake data set with all keys using Cartesian Join in PROC SQL */
proc sql;
    create table distinct_id as select distinct id from actual;
    create table distinct_key as select distinct key from actual;
    create table fake as select * from distinct_id,distinct_key;
quit;

/* Compare fake to actual */
data compared;
     merge fake actual(in=in2);
     by id key
     if not in2; /* This creates a data set compared of only those keys that are not in data set actual */
run;

Re: Missing Data Elements?

novinosrin — Fri, 11 Oct 2019 12:11:14 GMT


data have;
input (ID         KEY) ($);
cards;
00001    01
00001    02
00001    03
00001    04
00001    05
00001    06
00001    07
00002    01
00002    02
00002    04
00002    07
;

proc sql;
create table check as
select *
from have
group by id
having count(distinct key)<7;
quit;

/*Or if you want just a list of ID's that do not have 7 keys associated*/

proc sql;
create table check as
select distinct id
from have
group by id
having count(distinct key)<7;
quit;

Re: Missing Data Elements?

IgawaKei29 — Fri, 11 Oct 2019 12:20:27 GMT

Thank you both, appreciate the help. Both options worked for what I needed.

Re: Missing Data Elements?

sbxkoenk — Fri, 11 Oct 2019 12:41:50 GMT

Hello,

Produce a dataset that is complete (all keys present!).

Then check which of these keys are not present in your "real" dataset;

data work.all_keys;
LENGTH ID $ 5;
input ID $ 1-5;
cards;
00001
00002
;
run;
data work.all_keys;
 set work.all_keys;
 do key='01','02','03','04','05','06','07';
 output;
 end;
run;
proc sort data=work.all_keys; by ID key; run;
data work.real_keys;
LENGTH ID $ 5 key $ 2;
input ID $ 1-5 key $ 7-8;
cards;
00001 01
00001 02
00001 04
00001 07
00002 01
00002 02
00002 03
00002 04
00002 06
;
run;
proc sort data=work.real_keys; by ID key; run;
data missing_keys;
 merge work.all_keys(in=a) 
       work.real_keys(in=b); 
 by ID key;
 if a and NOT b then output;
run;
/* end of program */

Regards,

Koen