Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Count distinct values when missing fields/Notmissing fields

Reply
Occasional Contributor
Posts: 18

Count distinct values when missing fields/Notmissing fields

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:

PrescriptionsIndications
Pres1Indicat1
Pres2
Pres3
Pres4Indcat2
Pres5Indicat3

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

Valued Guide
Posts: 854

Re: Count distinct values when missing fields/Notmissing fields

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);

Occasional Contributor
Posts: 18

Re: Count distinct values when missing fields/Notmissing fields

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

Esteemed Advisor
Esteemed Advisor
Posts: 6,685

Re: Count distinct values when missing fields/Notmissing fields

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;

SAS Employee
Posts: 35

Re: Count distinct values when missing fields/Notmissing fields

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

Occasional Contributor
Posts: 11

Re: Count distinct values when missing fields/Notmissing fields

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.

Post a Question
Discussion Stats
  • 5 replies
  • 733 views
  • 8 likes
  • 5 in conversation