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.
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;
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.
my standard drug list table does not have the state variable, it only have the drug_code in there.
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
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;
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;
this code works perfectly. thank you for your help!
Julia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.