How to find observation ID with a blank value?

[maqre]

Hello everybody,

I have a database of Product, Country, Price and VAT, and I am trying to create a Macro, that will show me every single observation ID, where the Price has no value. How can I do that?

[data_null__]

WHERE missing(price);

using the viewer of your choice.

[Linlin]

data have;

infile cards missover;

input id price ;

cards;

1 20

2 .

3 30

4 .

5 50

6 .

;

proc sql noprint;

  select id into : ids separated by ' '

    from have

  where missing(price);

quit;

%put &ids;

/*  log file */

17    %put &ids;

2 4 6

[maqre]

But my database does not have an ID column.

[Linlin]

what do you mean by "single observation ID"?

[maqre]

I meant ID of the row.

[Linlin]

do you want the row number?

data have;

infile cards missover;

input id$ price ;

cards;

a 20

b .

c 30

d .

e 50

f .

;

data have;

  set have;

  n=_n_;

proc sql noprint;

  select n into : ids separated by ' '

    from have

  where missing(price);

quit;

%put &ids;

[Haikuo]

Hi LinLin, Long time no see. I believe this can also be done within one data step:

data have;

infile cards missover;

input id$ price ;

cards;

a 20

b .

c 30

d .

e 50

f .

;

data _null_;

  set have end=last;

   length _n $ 32767;

   retain _n;

   if missing(price) then _n=catx(' ',_n,_n_);

   if last then call symputx('ids', _n);

   drop _n;

run;

%put &ids;

Haikuo

[Linlin]

Hi Haikuo,

It is good to see you! 中秋节快乐！

[gra_in_aus]

If you have a lot of data that is stored on a database it may be better to use the following:

1. Create a unique variable as your ID... can be done as a view instead so you don't need to add an extra level of processing:

     It would probably be better to dot his on the database side. Or you can do it locally by either using PROC SQL or DATA Step:

e.g.

data work.prdsale / view=work.prdsale;

  set sasuser.prdsale;

  ID +1; /* << added to create a unique id*/

  if DIVISION =: "CONS" & PRODTYPE =: "OFF" & PRODUCT =: "DESK" then Actual = .; /* added to create missing vals for Actuals*/

run;

2. Then utilise PROC SUMMARY, this will use multi threading and can also be passed through to the database for In-Database processing if you are using SAS/CONNECT Engines...

options msglevel=i; /* displays in log whether threads are used */

proc summary

   data = work.prdsale (where=(missing(ACTUAL))) missing nway;

   class _all_;

   output out=work.XXX_Missing_Actual (drop=_freq_ _type_);

run;

3. Then create your Macro Var :

proc sql;

      select id into: macro_id separated by ", "

      from work.XXX_Missing_Actual ;

quit;

%put &macro_id;

TTFN

Graham