Hi everyone,
I would like to write a macro that takes an id as parameter and display the columns
of a dataset for the (unique) row having that id. If there is no observation for the given id,
i want to display the columns anyway with missing values.
Here is a simplified example of what i tried
data have;
input id 1. x 2.;
cards;
112
25
326
421
516
64
run;
%macro display_cols;
array columns (*) _NUMERIC;
do i=1 to dim(columns);
put vname(columns(i))=;
end;
%mend display_cols;
%macro infos_id(id);
data _NULL_;
/* If the id is not in the table, we display the columns anyway */
if eof and id=. then do;
id=&id;
output;
%display_cols;
end;
set have end=eof;
where id=&id.;
%display_cols;
run;
%mend infos_id;
%infos_id(2);
%infos_id(8);
The log shows the following message :
ERROR 124-185: The variable columns has already been defined.
If i comment the second %display_cols and test with a non-existing id, the columns displayed
are id and eof instead of id and x. I understand that it is because the "output" is before the "set have"
but i can't see how it can be done.
Is there a way to take advantage of the table structure to avoid explicitely listing all the columns
in case of a non-existing id ?
Thanks
The names of columns can easily be displayed by querying sashelp.vcolumn (data/proc step) or dictionary.columns (SQL).
Names of columns stay the same over all rows.
The output statement in a data _null_ is perfectly useless, as there is no dataset to output to.
I STRONGLY recommend to try everything in a pure data step first, and only after that gets you the desired result, start wrapping into a macro definition.
Since the array definition does not work before the set statement, you won't be able to solve this in the way you intended.
A possible solution might look like this:
%let id=8;
data _NULL_;
if eof and id = . then do;
id=&id;
put _all_;
stop;
end;
else do;
set have (where=(id=&id.)) end=eof;
put _all_;
end;
run;
You have a statement defining an array in your macro. Array definitions shall only appear once for each array in the data step. So take it out of the macro and write it into the data step.
The names of columns can easily be displayed by querying sashelp.vcolumn (data/proc step) or dictionary.columns (SQL).
Names of columns stay the same over all rows.
The output statement in a data _null_ is perfectly useless, as there is no dataset to output to.
I STRONGLY recommend to try everything in a pure data step first, and only after that gets you the desired result, start wrapping into a macro definition.
Since the array definition does not work before the set statement, you won't be able to solve this in the way you intended.
A possible solution might look like this:
%let id=8;
data _NULL_;
if eof and id = . then do;
id=&id;
put _all_;
stop;
end;
else do;
set have (where=(id=&id.)) end=eof;
put _all_;
end;
run;
Thank you for your answers and advises.
You are right about the ouptut statement which has no use here.
My example is a simplification of what i a m trying to achieve.
The macro will actually write html to the output stream and
the table columns are displayed in a html table so I need to
process each column individually.
I thought about dictionary.columns but tried to achieve the result this way
for the sake of conciseness.
You tell me that this is impossible because the set statement must preceed
any array declarations. This is what I wanted to know so i will follow your advice and use
dictionary.columns.
Thanks again
You could begin the DATA step this way:
data _null_;
if 0 then set have;
The condition 0 is always false, so this extra SET statement never executes. However, it serves a purpose by defining your variables so that you can define the array as you originally intended. (Also note, the variable list would be _numeric_, not _numeric.)
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.