DATA Step, Macro, Functions and more

where condition not satified in a data step

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

where condition not satified in a data step

[ Edited ]

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


Accepted Solutions
Solution
‎09-30-2016 06:52 AM
Super User
Posts: 6,940

Re: where condition not satified in a 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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,940

Re: where condition not satified in a data step

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎09-30-2016 06:52 AM
Super User
Posts: 6,940

Re: where condition not satified in a 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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 194

Re: where condition not satified in a data step

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

Super User
Posts: 5,083

Re: where condition not satified in a data step

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

Regular Contributor
Posts: 194

Re: where condition not satified in a data step

Great trick, thank you very much !
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 349 views
  • 4 likes
  • 3 in conversation