how to find the missing records from the starndard list

[juliajulia]

Hello,

I have a dataset with two variables: State and drug code. I want to print out a list of state who missed drug codes from the standard drug code list. the data set is list this:

state   drug_code

AL        1

AL         2

AL         3

AL         4

GA        2

GA        3

GA        4

GA        5

..

and the standard drug_code looks like this:

drug_code

1

2

3

4

5

so the final result will looks like this:

State  missing_drug_code

AL       5

GA     1

not sure how to write a sas code to get the result.

[Reeza]

Assuming your input data set is called have and your data is as shown, this would give you that list. This assumes that the list includes all states and at least one entry for every drug code. If the later is not true then this will not work, but there are other approach. CLASSDATA and PRELOADFMT are two options as is a standard merge.

proc freq data=have;
table state*drug_code / out=want(where=(count=0)) sparse;
run;

---

@juliajulia wrote:

Hello,

I have a dataset with two variables: State and drug code. I want to print out a list of state who missed drug codes from the standard drug code list. the data set is list this:

state   drug_code

AL        1

AL         2

AL         3

AL         4

GA        2

GA        3

GA        4

GA        5

..

and the standard drug_code looks like this:

drug_code

1

2

3

4

5

so the final result will looks like this:

State  missing_drug_code

AL       5

GA     1

not sure how to write a sas code to get the result.

---

[juliajulia]

my standard drug list table does not have the state variable, it only have the drug_code in there.

[Reeza]

So you don't have that first table shown? The PROC FREQ approach doesn't need a lookup table, if you can make those assumptions I mentioned initially. Did you try the code?

[juliajulia]

yes, I tried the code. but it only can show the drug_code that already have in the table. there are some drug codes are not in the first table. for example, the code 6 and 7 are not in the dataset have.

data have;

input state $ drug_code;

datalines;

AL 1

AL 2

AL 3

AL 4

GA 2

GA 3

GA 4

GA 5

;

run;

data drug_code;

input drugcode;

datalines;

1

2

3

4

5

6

7

;

run;

　

proc freq data=have;

table state*drug_code / out=want(where=(count=0)) sparse;

run;

I want the result like this:

State  Missing_code

AL     5

AL     6

AL     7

GA     1

GA     6

GA     7

[Ksharp · Accepted solution]

 

data have;
input state $ drug_code;
datalines;
AL 1
AL 2
AL 3
AL 4
GA 2
GA 3
GA 4
GA 5
;
run;

data drug_code;
input drug_code;
datalines;
1
2
3
4
5
;
run;
proc sql;
create table want as
select a.*
 from 
(
select * from
(select distinct state from have),(select distinct drug_code from drug_code)
) as a left join have as b on a.state=b.state and a.drug_code=b.drug_code
where b.drug_code is missing;
quit;

[Ksharp]

OR this one .

 

data have;
input state $ drug_code;
datalines;
AL 1
AL 2
AL 3
AL 4
GA 2
GA 3
GA 4
GA 5
;
run;

data drug_code;
input drug_code;
datalines;
1
2
3
4
5
;
run;
proc sql;
create table want as
select * from
(select distinct state from have),(select distinct drug_code from drug_code)
except
select * from have;
quit;

[juliajulia]

this code works perfectly. thank you for your help!

Julia