topic Re: how to NOT manually type all 50 values in a macro in SAS Programming

how to NOT manually type all 50 values in a macro

HitmonTran — Thu, 17 Jun 2021 00:20:42 GMT

Hello,

I have a dataset with 50 different geographic locations (variable "COUNTRY") I need to subset. Rather than creating a macro and manually calling all 50 locations, I was wondering if there's an efficient process where I can collect all the values of variable "COUNTRY" and produce ONE rtf output (each page will have different locations)? I know you can use BY statement in proc report but my original program is a bit more complex than the example shown below.

%macro location(location=);
proc sort data=cars (keep=model color country);
   by country;
   where country=&location;
run;

proc sql;
	select (strip(put(count(distinct model), best.))) into :var1 from cars;
quit;

%put &var1;
 

ods rtf "c:/desktop/carmodel.rtf";
title1= "2021 car models in &location";
proc report data=cars;
    column model ;
    define model display "&location (&var1)";
run;

%mend location;

%location (location='USA');
%location (location='MEX');
%location (location='CHN');
%location (location='CAN');
%location (location='JAP');
%location (location='KOR');
%location (location='RUS');
%location (location='AFR');
%location (location='AUS');
%location (location='PR');
%location (location='BRA');
%location (location='IND');
%location (location='AMS');
%location (location='ENG');

Re: how to NOT manually type all 50 values in a macro

ballardw — Thu, 17 Jun 2021 01:00:43 GMT

I think this is the second time you have said you have to "subset" something.

When you use a phrase like "manually calling all 50 locations" I think you a misusing "subset".

How about providing some example data and your "original program" that is more complex. It may be that your complex program is part of the problem.

And there are probably a hundred programs here that show how to do this by pulling all the distinct values from a data set into a macro variable then using them.

%macro dummy;

proc sql noprint;
   select distinct origin into :originlist separated by ' '
   from sashelp.cars
   ;
quit;

ods rtf file="c:\desktop\carmodel.rtf";

%do i = 1 %to %sysfunc(countw(&originlist.));

   %let location = %scan(&originlist.,&i);
   proc sql noprint;
   	select count(*) into :var1 
      from (select distinct model from sashelp.cars
           where origin = "&location." )
      
      ;
   quit;

   %put &var1;
    

   proc report data=sashelp.cars;
       where origin = "&location.";
       column model ;
       define model /display "&location. (&var1)";
   run;

%end; /* end the loop I*/

ods rtf close;
%mend;

%dummy;

What you were attempting was going to overwrite the output rtf file. You need to make sure if want one document that the ods rtf/ ods rtf close sandwiches ALL the output calls you want to make.

The first sql builds a list and the %do loop shows how to iterate over each value in that list.

Re: how to NOT manually type all 50 values in a macro

Kurt_Bremser — Thu, 17 Jun 2021 07:56:30 GMT

Create a dataset with locations, and use CALL EXECUTE from that (the macro for a single location should have code to start a new page):

proc sort
  data=have (keep=location)
  out=control
  nodupkey
;
by location;
run;

ods rtf .....;

data _null_;
set control;
call execute(cats('%nrstr(%location(location=',location,'))'));
run;

ods rtf close;

Re: how to NOT manually type all 50 values in a macro

PaigeMiller — Thu, 17 Jun 2021 12:23:59 GMT

As soon as you start specifying that the solution MUST include a macro, you lose the opportunity to find simpler solutions. I assume the reason you believe you need a macro is this line:

define model display "&location (&var1)";

but there are other ways to achieve this, and without macros.

For example, a BY variable can be placed in a title or footnote in such a manner that the title or footnote changes each time the BY variable changes. So you don't need &location as a macro variable to display above each column, it could appear in the title and change appropriately when the BY variable changes. You could do the same with &var1, compute it and then add it into a title or footnoate, and suddenly, no macros are needed.

Now this isn't EXACTLY the output you requested with your macro code, but it contains the same information; and thus I repeat my opening point, sometimes there are much simpler approaches if you can avoid specifying that the code MUST contain certain elements.

Re: how to NOT manually type all 50 values in a macro

HitmonTran — Fri, 18 Jun 2021 16:17:19 GMT

that works, but only for the first subgroup. &var1 retained its values to the next subgroup. Did I do something wrong?

this is my actual code to. "&trt1"="&var1" in this situation:

proc sql;
	    select (strip(put(count(distinct usubjid), best.))) into :trt1 from adsl_addv1 where TRT01pN=1 group by siteID ;
		 
quit;


%put &trt1 ;

Re: how to NOT manually type all 50 values in a macro

tarheel13 — Fri, 18 Jun 2021 21:26:26 GMT

Can you post the rest of your code so we can see?