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?
WHERE missing(price);
using the viewer of your choice.
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
But my database does not have an ID column.
what do you mean by "single observation ID"?
I meant ID of the row.
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;
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
Hi Haikuo,
It is good to see you! 中秋节快乐!
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 ¯o_id;
TTFN
Graham
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!
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.