topic Re: where condition not satified in a data step in SAS Programming

where condition not satified in a data step

gamotte — Fri, 30 Sep 2016 10:09:50 GMT

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

Re: where condition not satified in a data step

Kurt_Bremser — Fri, 30 Sep 2016 10:14:22 GMT

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.

Re: where condition not satified in a data step

Kurt_Bremser — Fri, 30 Sep 2016 10:32:49 GMT

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;

Re: where condition not satified in a data step

gamotte — Fri, 30 Sep 2016 10:52:33 GMT

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

Re: where condition not satified in a data step

Astounding — Fri, 30 Sep 2016 11:21:40 GMT

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.)

Re: where condition not satified in a data step

gamotte — Fri, 30 Sep 2016 11:44:49 GMT

Great trick, thank you very much !