How to mark calculated observations as missing in proc sql

Phil_NZ — Thu, 18 Feb 2021 05:07:15 GMT

Hi SAS Users.

I have a code below to create the table work.industry_mtbv

proc sql;
create table work.industry_mtbv as
  select year, INDC3, 
		median(wMAR_TO_BOO) as median_mtbv
  from canus
  group by INDC3,year
  ;
quit;

This means that median_mtbv will be calculated for each INDC3( industry) each year.

I want to add one more condition that: if INDC3 in ('NA', 'UNCLS', 'UQESQ') then median_mtbv=. (setting missing for median_mtbv if INDC3 is one of these three industries)

Can you please suggest to me how can I adjust the proc SQL above to add this condition?

Warmest regards.

Re: How to mark calculated observations as missing in proc sql

andreas_lds — Thu, 18 Feb 2021 05:41:55 GMT

Maybe the CASE-statement could be used, but imho this makes sql even less readable.

I would not use sql for anything, that could be solved by using a normal proc, so:

proc summary data=canus nway;
  class indc3 year;
  var wMAR_TO_BOO;
  output out=work.industry_mtbv(drop= _type_ _freq_) median=median_mtbv;
run;

data work.industry_mtbv;
  set work.industry_mtbv;

  if indc3 in ('NA', 'UNCLS', 'UQESQ') then median_mtbv = .;
run;

topic How to mark calculated observations as missing in proc sql in SAS Programming

How to mark calculated observations as missing in proc sql

Re: How to mark calculated observations as missing in proc sql