BookmarkSubscribeRSS Feed
vasireddyajay
Fluorite | Level 6

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

5 REPLIES 5
Steelers_In_DC
Barite | Level 11

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Employee

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
Obsidian | Level 7

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 5082 views
  • 8 likes
  • 5 in conversation