Count distinct values when missing fields/Notmissing fields

[vasireddyajay]

Hi,

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.

Example:

Prescriptions	Indications
Pres1	Indicat1
Pres2
Pres3
Pres4	Indcat2
Pres5	Indicat3

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.

Thanks.

Regards,

Ajay

[Steelers_In_DC]

Here's a simplified version of what you are asking, if you are looking for something more than this let me know:

data have;

infile cards dsd;

input Prescriptions $ Indications $;

cards;

Pres1,Indicat1

Pres2,,

Pres3,,   

Pres4,Indcat2

Pres5,Indicat3

;

run;

proc sql;

create table want1 as

select distinct prescriptions

from have

where missing(indications);

proc sql;

create table count as

select distinct count(prescriptions) as count

from have

where missing(indications);

proc sql;

create table want2 as

select distinct prescriptions,indications

from have

where not missing(indications);

[vasireddyajay]

Hi Mark,

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.

Kind regards,

Ajay

[RW9]

Hi,

You give no indication of what you want your output to look like, so here are two examples:

data have;

  prescriptions="Press1"; Indications="Indicat1"; output;

  prescriptions="Press2"; Indications=""; output;

  prescriptions="Press3"; Indications=""; output;

  prescriptions="Press4"; Indications="Indicat2"; output;

  prescriptions="Press5"; Indications="Indicat3"; output;

run;

proc sql;

  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

  on      1=1;

quit;

proc sql;

  create table WANT2 as

  select  "Distinct Prescriptions with Missing Indication" as COL1 length=200,

          count(distinct PRESCRIPTIONS) as RESULT

  from    HAVE

  where   INDICATIONS is null

  union all

  select  "Distinct Prescriptions with Indication",

          count(distinct PRESCRIPTIONS)

  from    HAVE

  where INDICATIONS is not null;

quit;

[Lorrie_SAS]

Hi Ajay,

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:

http://support.sas.com/documentation/cdl/en/vaug/68027/HTML/default/viewer.htm#p0ef9qegein6kon1dy9qm...

If you prefer, you can also modify the code directly on the ‘Code’ tab in the data query. You can find more information here:

http://support.sas.com/documentation/cdl/en/vaug/68027/HTML/default/viewer.htm#p1bhe7s7u4l7egn1d2ahw...

(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.

Best regards,

Lorrie

[mojerry_realdolmen]

the easiest way is to make a calculated item and working with IF then else case.

IF ( 'column'n Missing )
RETURN 1
ELSE 0

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.