topic Re: How to find observation ID with a blank value? in SAS Programming

How to find observation ID with a blank value?

maqre — Sat, 14 Sep 2013 22:39:32 GMT

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?

Re: How to find observation ID with a blank value?

data_null__ — Sat, 14 Sep 2013 22:45:09 GMT

WHERE missing(price);

using the viewer of your choice.

Re: How to find observation ID with a blank value?

Linlin — Sat, 14 Sep 2013 22:46:53 GMT

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

Re: How to find observation ID with a blank value?

maqre — Sat, 14 Sep 2013 23:13:20 GMT

But my database does not have an ID column.

Re: How to find observation ID with a blank value?

Linlin — Sat, 14 Sep 2013 23:21:28 GMT

what do you mean by "single observation ID"?

Re: How to find observation ID with a blank value?

maqre — Sat, 14 Sep 2013 23:24:32 GMT

I meant ID of the row.

Re: How to find observation ID with a blank value?

Linlin — Sat, 14 Sep 2013 23:43:37 GMT

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;

Re: How to find observation ID with a blank value?

Haikuo — Sun, 15 Sep 2013 00:38:05 GMT

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

Re: How to find observation ID with a blank value?

Linlin — Sun, 15 Sep 2013 00:48:49 GMT

Hi Haikuo,

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

Re: How to find observation ID with a blank value?

gra_in_aus — Sun, 15 Sep 2013 01:11:12 GMT

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