06-10-2015 05:57 AM
I have a dataset, where I have to make relation with missing to Notmissing for quality indicators. How ever, I didnot find the solution to count distinct observation for var1 when var2 is missing. Or count distinct observations of var1 when var2 is not missing.
So, I am looking for a solution in SAS VA where I could 2 counts
1.) All distinct count of prescription when the indications are missing
2.) All distinct count of prescriptions when indications are not missing
Any tips will be appreciated.
06-10-2015 08:13 AM
Here's a simplified version of what you are asking, if you are looking for something more than this let me know:
infile cards dsd;
input Prescriptions $ Indications $;
create table want1 as
select distinct prescriptions
create table count as
select distinct count(prescriptions) as count
create table want2 as
select distinct prescriptions,indications
where not missing(indications);
06-10-2015 08:31 AM
Ya, this is exactly what I wanted. But, how to achieve this in SASVA, as there is no "Where/When" condition in aggregated measure.
Or is it possible to write this code in SASVA to create temp tables?
Thank you very much.
06-10-2015 08:31 AM
You give no indication of what you want your output to look like, so here are two examples:
prescriptions="Press1"; Indications="Indicat1"; output;
prescriptions="Press2"; Indications=""; output;
prescriptions="Press3"; Indications=""; output;
prescriptions="Press4"; Indications="Indicat2"; output;
prescriptions="Press5"; Indications="Indicat3"; output;
create table WANT as
select count(distinct A.PRESCRIPTIONS) as DIST_P_MISS,
count(distinct B.PRESCRIPTIONS) as DIST_P_NON
from (select * from HAVE where INDICATIONS is null) A
full join (select * from HAVE where INDICATIONS is not null) B
create table WANT2 as
select "Distinct Prescriptions with Missing Indication" as COL1 length=200,
count(distinct PRESCRIPTIONS) as RESULT
where INDICATIONS is null
select "Distinct Prescriptions with Indication",
where INDICATIONS is not null;
06-10-2015 09:43 AM
Not sure if you’ve considered using the data builder in SASVA, but it sounds like it could be a good option for what you need to accomplish. From the data builder, you can create data queries and filter using the ‘Where’ tab. You can find more information here:
If you prefer, you can also modify the code directly on the ‘Code’ tab in the data query. You can find more information here:
(If you use this method, be sure to save the data query at least once before you unlock the code)
If you need more information, please let me know.
05-03-2017 09:40 AM
the easiest way is to make a calculated item and working with IF then else case.
IF ( 'column'n Missing )
this will flag all your missing columns. The sum will tell you how many cells are missing.
you can use the NotMissing to count the number of filled cells/rows.