Macro

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

Macro

Hi....I have a macro which errors in the dataset for the column variable Carrier_ID which seems to work fine. Now I want to modify it to include other column variables and report the results in the same table where each table row reports the results for each Column variable. I was thinking that a macro with a loop from a list where the list contains the names of the column variables. Another problem is that some of the column variables are numeric and some are character. Can this be solved or is there a better way to achieve the end results....thanks..

%macro countm(col);

    count(&col) "Valid Responses",

     nmiss(&col) "Missing or NOT VALID Responses",

     count(case

            when &col=.n  then "count me"

            end) "Coded as NO ANSWER",

   count(case

            when &col=.x  then "count me"

            end) "Coded as NOT VALID answers",

   count(case

            when &col=.  then "count me"

            end) "Data Entry Errors"

%mend;

  proc sql;

   title 'Counts for Each Type of Missing Response';

   select count(*)  "Total No. of Rows",

           %countm(CARRIER_ID)

      from WORK.&fil;


Accepted Solutions
Solution
‎12-12-2013 12:10 PM
Super User
Super User
Posts: 7,079

Re: Macro

Usually when I do that I make the macro have one parameter that accepts a space delimited list of variables (the same as you would use in a normal SAS statement like VAR or KEEP).

%macro countm(cols);

%local i col ;

%do i=1 %to %sysfunc(countw(&cols,%str( )));

  %let col=%scan(&cols,&i,%str( ));

.... your current macro body ....

%end;

%mend countm;


To make so that the results are stacked vertically instead of horizontally you would need to UNION separate queries.  Not sure how efficient it would be and note that PROC SQL has a limit on the number of table references you can have.

%macro countm(ds,cols);

%local i col ;

%do i=1 %to %sysfunc(countw(&cols,%str( )));

  %let col=%scan(&cols,&i,%str( ));

%if &I > 1 %then UNION ;

select "&col" length=32 "Variable Name"

     , count(&col) "Valid Responses"

     , nmiss(&col) "Missing or NOT VALID Responses"

      , count(case

            when &col=.n  then "count me"

            end) "Coded as NO ANSWER"

     , count(case

            when &col=.x  then "count me"

            end) "Coded as NOT VALID answers"

     , count(case

            when &col=.  then "count me"

            end) "Data Entry Errors"

from &ds

%end;

%mend countm;

proc sql ;

%countm(WORK.&fil,var1 varB) ;

quit;

View solution in original post


All Replies
Super User
Posts: 19,877

Re: Macro

Proc freq with a custom format is probably a better method

See the paper below that does a great job. This way you can hit all variables with one proc.

http://www2.sas.com/proceedings/sugi30/001-30.pdf

Solution
‎12-12-2013 12:10 PM
Super User
Super User
Posts: 7,079

Re: Macro

Usually when I do that I make the macro have one parameter that accepts a space delimited list of variables (the same as you would use in a normal SAS statement like VAR or KEEP).

%macro countm(cols);

%local i col ;

%do i=1 %to %sysfunc(countw(&cols,%str( )));

  %let col=%scan(&cols,&i,%str( ));

.... your current macro body ....

%end;

%mend countm;


To make so that the results are stacked vertically instead of horizontally you would need to UNION separate queries.  Not sure how efficient it would be and note that PROC SQL has a limit on the number of table references you can have.

%macro countm(ds,cols);

%local i col ;

%do i=1 %to %sysfunc(countw(&cols,%str( )));

  %let col=%scan(&cols,&i,%str( ));

%if &I > 1 %then UNION ;

select "&col" length=32 "Variable Name"

     , count(&col) "Valid Responses"

     , nmiss(&col) "Missing or NOT VALID Responses"

      , count(case

            when &col=.n  then "count me"

            end) "Coded as NO ANSWER"

     , count(case

            when &col=.x  then "count me"

            end) "Coded as NOT VALID answers"

     , count(case

            when &col=.  then "count me"

            end) "Data Entry Errors"

from &ds

%end;

%mend countm;

proc sql ;

%countm(WORK.&fil,var1 varB) ;

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 206 views
  • 0 likes
  • 3 in conversation