BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gamotte
Rhodochrosite | Level 12

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

 

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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;

 

gamotte
Rhodochrosite | Level 12

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

Astounding
PROC Star

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

gamotte
Rhodochrosite | Level 12
Great trick, thank you very much !

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
  • 5 replies
  • 1113 views
  • 4 likes
  • 3 in conversation