BookmarkSubscribeRSS Feed
maqre
Calcite | Level 5

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?

9 REPLIES 9
data_null__
Jade | Level 19

WHERE missing(price);

using the viewer of your choice.

Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

But my database does not have an ID column.

Linlin
Lapis Lazuli | Level 10

what do you mean by "single observation ID"?

maqre
Calcite | Level 5

I meant ID of the row.

Linlin
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

Hi Haikuo,

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

gra_in_aus
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 890 views
  • 1 like
  • 5 in conversation