topic Re: how to find the missing records from the starndard list in SAS Programming

how to find the missing records from the starndard list

juliajulia — Tue, 09 Apr 2019 19:39:36 GMT

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

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.

Re: how to find the missing records from the starndard list

Reeza — Tue, 09 Apr 2019 19:47:51 GMT

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.

Re: how to find the missing records from the starndard list

juliajulia — Tue, 09 Apr 2019 19:53:41 GMT

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

Re: how to find the missing records from the starndard list

Reeza — Tue, 09 Apr 2019 19:55:55 GMT

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?

Re: how to find the missing records from the starndard list

juliajulia — Tue, 09 Apr 2019 20:54:56 GMT

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;

;

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

Re: how to find the missing records from the starndard list

Ksharp — Wed, 10 Apr 2019 13:07:12 GMT

 

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;

Re: how to find the missing records from the starndard list

Ksharp — Wed, 10 Apr 2019 13:09:07 GMT

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;

Re: how to find the missing records from the starndard list

juliajulia — Wed, 10 Apr 2019 14:58:36 GMT

this code works perfectly. thank you for your help!

Julia