topic Re: Simply macro in in SAS Programming

Simply macro in

ybz12003 — Thu, 09 May 2019 16:58:45 GMT

Hello experts:

I have a macro program shown below. As you could see every macro is very similar, the difference is in the combination of different numbers. I have about 20 macros (%create_lab) and too long to list here. I am wondering if there is a way to simplify the LONG code. Thanks.

%macro error_check;

	%let X=1;
	
	proc sql;
	create table lab&X as
 	select State,ID,age,GEM1,GEM1_CNT
 	from have  
 	where (State in(1,2,8) and GEM1 not in(0,2)) or
	(State in(3,5,6) and GEM1 not in(0,8)) or 
	(State in(1,2,8) and GEM1=1 and GEM1_CNT is null)
	order by State,ID;

%macro create_lab;
	proc print data=lab&X noobs label;
		by State;
		title2 "Check &X:&&&lab_&X";
	run;
%mend create_lab;
%create_lab;

		%let i=(&X + 1 );
		%let X=%eval(&i);

		proc sql; 
			create table lab&X as
		 	select State,ID,age,GEM2,GEM2_CNT
		 	from have
		 	where (State in(3,4,6,8) and GEM2 not in(0,1,2)) or
				(State in(1,2,5) and GEM2 not in(8)) or
			    (State in(4,8) and GEM2=1 and GEM2_CNT is null)
		 	order by State,ID;
		%create_lab;


		%let i=(&X + 1 );
		%let X=%eval(&i);

		proc sql;
			create table lab&X as
		 	select State,ID,age,GEM3,GEM3_CNT
		 	from have 
		 	where (State in(3,4,6) and GEM3 not in(8)) or
			(State in(2,5,8) and GEM3 not in (0,1,2)) or
			(State in(1,2,5) and GEM3=1 and GEM3_CNT is null) 

		 	order by State,ID;
		%create_lab;

		%let i=(&X + 1 );
		%let X=%eval(&i);

		proc sql;
			create table lab&X as
		 	select State,ID,age,GEM4,GEM4_CNT
		 	from have 
		 	where (State in(3,4,9) and GEM4 not in(6,8)) or
			(State in(0,5,9) and GEM4 not in (6,9)) or
			(State in(6,8) and GEM4=1 and GEM4_CNT is null) 

		 	order by State,ID;
		%create_lab;


		%let i=(&X + 1 );
		%let X=%eval(&i);
		.
		.
		.
		%create_lab;


		%let i=(&X + 1 );
		%let X=%eval(&i);
		.
		.
		.
		%create_lab;

%mend error_check;

Re: Simply macro in

PaigeMiller — Thu, 09 May 2019 17:04:13 GMT

@ybz12003 wrote:

Hello experts:

I have a macro program shown below. As you could see every macro is very similar, the difference is in the combination of different numbers. I have about 20 macros (%create_lab) and too long to list here. I am wondering if there is a way to simplify the LONG code. Thanks.

I guess I would like you to be more specific. "Combination of different numbers"?

Is that this part of the macro?

where (State in(3,4,6,8) and GEM2 not in(0,1,2)) or
				(State in(1,2,5) and GEM2 not in(8)) or
			    (State in(4,8) and GEM2=1 and GEM2_CNT is null)

If so, you could have arguments to macro which allow the macro to run on any valid combination of numbers.

Something like this:

%macro error_check(state=, gem2=);

%macro error_check(where_clause=);

Finally, please go back to the first post in this thread and provide a meaningful title. Thank you.

Re: Simply macro in

ybz12003 — Thu, 09 May 2019 17:17:09 GMT

I have a big macro (error_check) and a small macro (create_lab). And my goal is creating another macro before the "%let i=(&X + 1 );
%let X=%eval(&i);" to avoid repeat codes shown below. I hope I could only have one code could cover all 20 repeating Proc SQL.

% macro repeat(...);

%let i=(&X + 1 );
%let X=%eval(&i);

proc sql;
.
.
%create_lab;

%mend repeat (...);
%repeart (gem1...);
%repeat (gem2...);
%repeat (gem3...);

Re: Simply macro in

PaigeMiller — Thu, 09 May 2019 17:33:53 GMT

Well, it's still not clear to me exactly what you are looking for.

I specifically asked, and did not receive an answer, to this question:

I guess I would like you to be more specific. "Combination of different numbers"?

Is that this part of the macro?
where (State in(3,4,6,8) and GEM2 not in(0,1,2)) or
				(State in(1,2,5) and GEM2 not in(8)) or
			    (State in(4,8) and GEM2=1 and GEM2_CNT is null)

Re: Simply macro in

ybz12003 — Thu, 09 May 2019 17:39:38 GMT

Yes

Re: Simply macro in

ballardw — Thu, 09 May 2019 17:42:04 GMT

I think if this were my data and I was concerned about (fixed known) combinations of values that I might be strongly tempted to create a temporary variable or two by concatenating the two variables with a delimiter character. Then make a format using that know list to allow detecting valid (or invalid) combinations.

This approach may be even stronger if your values of Gem1, Gem2, etc take the same range of valid and invalid combinations. Something along the line of

proc format library=work;
value $stgem1_ 
'1_1', '1_2', '2_2'= 'Valid'
other = 'Invalid'
;
value $stgem2_ 
'2_1', '3_2', '2_2'= 'Valid'
other = 'Invalid'
;
run;

data example;
   do st= 1 to 3;
   do gem1= 0 to 2;
   do gem2= 0 to 2;
      statusgem1= put( catx('_', st, gem1), stgem1_.);
      statusgem2= put( catx('_', st, gem2), stgem2_.);
      output;
   end;
   end;
   end;
run;

That moves the logic of combination out of possibly awkward to maintain if/then/else statements and can greatly simplify or remove the need for macro code at all.

Re: Simply macro in

PaigeMiller — Thu, 09 May 2019 18:32:43 GMT

Perhaps something like this:

%macro error_check(whereclause=);

	%let X=1;
	
	proc sql;
	create table lab&X as
 	select State,ID,age,GEM1,GEM1_CNT
 	from have  
 	where &whereclause
	order by State,ID;

%mend;

%error_check(whereclause=%nrstr((State in(1,2,8) and GEM1 not 
    in(0,2)) or (State in(3,5,6) and GEM1 not in(0,8)) or 
	(State in(1,2,8) and GEM1=1 and GEM1_CNT is null)))

Re: Simply macro in

ybz12003 — Thu, 09 May 2019 18:45:28 GMT

If use

&whereclause

I still need to generate 20 codes cause I have 20 GEMs.

Re: Simply macro in

ybz12003 — Thu, 09 May 2019 18:48:00 GMT

Gem1 to Gem20 is just the sample name I gave in this test. The actual GEM names are various not discipline.

Re: Simply macro in

ybz12003 — Thu, 09 May 2019 19:03:58 GMT

Is it possible I could generate 5 groups of numbers? Something is like below. However, I don't know if I could add () into a new macro.

%macro repeat (Gem, number1, number2, number3, number4, number5);
		%let i=(&X + 1 );
		%let X=%eval(&i);

		proc sql; 
			create table lab&X as
		 	select State,ID,age,&GEM,&GEM_CNT
		 	from have
		 	where (State in &number1 and &GEM not in &number2 ) or
				(State in &number3 and &GEM not in &number4) or
			    (State in &number5 and &GEM=1 and &GEM_CNT is null)
		 	order by State,ID;
		%create_lab;
%mend repeat;
%repeat ( gem1, (3,4,6,8), (0,1,2), (1,2,5), (8), (4,8) );
%repeat ( gem2, (3,4,6,8), (0,1,2), (1,2,5), (8), (4,8) );
%repeat ( gem3, (3,4,6), (8), (2,5,8), (0,1,2), (1,2,5));

Re: Simply macro in

PaigeMiller — Thu, 09 May 2019 19:04:44 GMT

@ybz12003 wrote:

If use
&whereclause
I still need to generate 20 codes cause I have 20 GEMs.

Again I'm confused. I thought the problem was to reduce the many macros into a single macro, which I have provided a possible solution. Then you call the macro as many times as necessary.

Re: Simply macro in

ballardw — Thu, 09 May 2019 21:37:01 GMT

@ybz12003 wrote:

Gem1 to Gem20 is just the sample name I gave in this test. The actual GEM names are various not discipline.

????

Re: Simply macro in

ballardw — Thu, 09 May 2019 21:52:43 GMT

Data

Rules

Then code.

I don't know what your starting data looks like.

From your code I am not willing to try to spend time backtracking as to what a general rule for any of this might be

And don't even know what the desired result is supposed to look like

If at all possible do not include special characters in macro parameters such as () or comma. The IN operator does not require a comma separator (since SAS 9.0 or so). So do not include them in parameters.

Since you know that your are going to use the values in an IN comparison then leave the () with the IN code to make things clearer about what is passed into the macro

%macro repeat (Gem, number1, number2, number3, number4, number5);
		%let i=(&X + 1 );
		%let X=%eval(&i);

		proc sql; 
			create table lab&X as
		 	select State,ID,age,&GEM,&GEM_CNT
		 	from have
		 	where (State in (&number1) and &GEM not in (&number2) ) or
				(State in (&number3) and &GEM not in (&number4) ) or
			    (State in (&number5) and &GEM=1 and &GEM_CNT is null)
		 	order by State,ID;
                %quit; /* unless you know that creat_lab is supposed to be part of the 
                        same Proc sql call*/
		%create_lab;
%mend repeat;
%repeat(  gem1 ,  3 4 6 8  ,  0 1 2  ,  1 2 5 ,   8  ,  4 8   );
%repeat(  gem2 ,  3 4 6 8 ,   0 1 2 ,   1 2 5 ,   8  ,  4 8   );
%repeat(  gem3 ,  3 4 6  ,  8  ,  2 5 8 ,   0 1 2 ,   1 2 5  );

I would be very careful about use of macro variables such as your X above that are not explicitly passed into the macro.

Also be very wary of not explicitly ending Procs or data step calls as sometimes depending on your macro language you may have an unexpected boundary (or not boundary in some cases) when calling other macros.

Re: Simply macro in

ScottBass — Fri, 10 May 2019 01:58:47 GMT

A large part of your problem with this thread is you haven't asked a clear question, or clear answers to other folks' questions.

In any case, assuming I've properly guessed what you're trying to do, this is how I'd approach it:

data metadata;
   length id 8 where $1000;
   infile datalines;
   input;
   id+1;
   where=_infile_;
   datalines4;
(State in (1,2,8)   and GEM1 not in (0,2))   or (State in (3,5,6) and GEM1 not in (0,8))   or (State in (1,2,8) and GEM1=1 and GEM1_CNT is null)
(State in (3,4,6,8) and GEM2 not in (0,1,2)) or (State in (1,2,5) and GEM2 not in (8))     or (State in (4,8)   and GEM2=1 and GEM2_CNT is null)
(State in (3,4,6)   and GEM3 not in (8))     or (State in (2,5,8) and GEM3 not in (0,1,2)) or (State in (1,2,5) and GEM3=1 and GEM3_CNT is null)
(State in (3,4,9)   and GEM4 not in (6,8))   or (State in (0,5,9) and GEM4 not in (6,9))   or (State in (6,8)   and GEM4=1 and GEM4_CNT is null)
;;;;
run;

%macro code;
   %let id=%trim(id);
   %let where=%trim(&where);

   proc sql;
      create table lab&id as
      select State,ID,age,GEM1,GEM1_CNT
      from have
      where &where
      order by State,ID;
    quit;

    proc print data=lab&id noobs label;
      by State;
      title2 "Check &id:lab&id";
    run;
%mend;
%loop_control(control=metadata)

Download these macros:

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

https://github.com/scottbass/SAS/blob/master/Macro/parmv.sas