topic Re: Count distinct values when missing fields/Notmissing fields in SAS Visual Analytics

Count distinct values when missing fields/Notmissing fields

vasireddyajay — Wed, 10 Jun 2015 09:57:25 GMT

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

Re: Count distinct values when missing fields/Notmissing fields

Steelers_In_DC — Wed, 10 Jun 2015 12:13:53 GMT

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

Re: Count distinct values when missing fields/Notmissing fields

vasireddyajay — Wed, 10 Jun 2015 12:31:17 GMT

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

Re: Count distinct values when missing fields/Notmissing fields

RW9 — Wed, 10 Jun 2015 12:31:37 GMT

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;

Re: Count distinct values when missing fields/Notmissing fields

Lorrie_SAS — Wed, 10 Jun 2015 13:43:15 GMT

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#p0ef9qegein6kon1dy9qm4szawnp.htm

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#p1bhe7s7u4l7egn1d2ahwvqw34is.htm

(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

Re: Count distinct values when missing fields/Notmissing fields

mojerry_realdolmen — Wed, 03 May 2017 13:40:22 GMT

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.