topic Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset? in SAS Programming

How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Carmen_Miranda — Wed, 08 Nov 2023 05:17:52 GMT

Hello! I'm rather new at SAS and I'm a little stumped on how best to do this.

I have the following dataset, called region_input (apologies for using a photo, the data step version SAS macro doesn't work on my SAS due to needing to get github information)

I want to create a DO loop that takes the value of the Region column at each row, assigns that to a macro variable, and then performs a number of other steps using that macro variable, before moving on to the next row and repeating.
I am having a lot of trouble figuring out how to reference the first row, however. So far I have :

%macro importer;
%do i=1 to 7;
data _null_;
set region_input;
call symput("region_name", Region);
run;
%end;
%mend importer;

But this doesn't work and I am utterly stumped on how to progress, or even really what to search in the help files.

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

sbxkoenk — Wed, 08 Nov 2023 09:36:27 GMT

You want sthg. like this??

data _NULL_;
 if 0 then set work.region_input nobs=count;
 call symput('NumberOfRegions',trim(left(put(count,12.))));
 STOP;
run;
%PUT &=NumberOfRegions;

%macro importer;
 %do i=1 %to &NumberOfRegions.;
data _null_;
 set work.region_input(firstobs=&i. obs=&i.);
 call symputx("region_name", Region);
run;
%PUT &=region_name;
 %end;
%mend  importer;

Koen

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Astounding — Wed, 08 Nov 2023 10:03:04 GMT

It's likely that CALL EXECUTE would simplify the process (instead of CALL SYMPUT). But we would need to know more about the analysis that gets performed for each REGION.

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

sbxkoenk — Wed, 08 Nov 2023 10:23:57 GMT

@Astounding wrote:
It's likely that CALL EXECUTE would simplify the process (instead of CALL SYMPUT). But we would need to know more about the analysis that gets performed for each REGION.

I absolutely agree with @Astounding .
Although I don't like CALL EXECUTE that much.
I always do "data-driven dynamic code generation" instead ...

using data _NULL_ step with File stmt. and several Put statements

followed by a %INCLUDE of the generated code.

Koen

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

PaigeMiller — Wed, 08 Nov 2023 12:05:40 GMT

How about this:

%macro importer;
proc sql noprint;
    select region into :regions separated by ' ' from region_input;
quit;
%do i=1 to &sqlobs;
%let this_region=%scan(&regions,&i,%str( ));
/* Your analysis of each region goes here */
%end;
%mend importer;

Which brings us to the big question in my mind — why not use BY statements, and perform analyses with the command

BY REGION;

All of this other manipulation in my code at the top, and everyone else's code, needs to be justified by answering: Why doesn't BY work here?

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Carmen_Miranda — Wed, 08 Nov 2023 22:06:28 GMT

Oh wow, this one's really clever! I'm not very familiar with SAS SQL, but this works really well.

There is one specific reason why I'm not using BY: I actually kinda forgot it existed. I started working in SAS 3 weeks ago and I've had so many new things put into my brain that I'm forgetting the first things I learned! Something of a grim start to the language, eh?

Thank you so much for the help with this. I'll give BY a shot and see if I can get it working!

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Carmen_Miranda — Wed, 08 Nov 2023 22:10:16 GMT

Oh, I see how this works! That's super clever, I think the tutorial I had been following skipped over the firstobs option in set statements. (That or I've forgotten it already...)

Thank you so much for the help. I think the way of dynamically adjusting the number of regions is really awesome, too, as I was going to have to figure that one out myself down the line.

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Tom — Wed, 08 Nov 2023 22:33:53 GMT

DO NOT use the ANCIENT CALL SYMPUT() function unless it is critical that leading and/or trailing spaces actually be put into the macro variable.

Use the NORMAL CALL SYMPUTX() function instead. It has only been around about 25 years now.

Also look at the CATS() function for generating a unique macro variable name for each observation's value of REGION.

data _null_;
  set region_input;
  call symputx(cats('region_name',_n_),Region);
run;

Re: How do I create a macro do loop that sets a macro variable based on an entry in a dataset?

Carmen_Miranda — Thu, 09 Nov 2023 04:41:05 GMT

Oh, that's super cool! I haven't heard of CATS() before, I think that's going to be super useful.

Also I have the sneaking suspicion that my organisation's in-house SAS tutorial might actually predate me as a person, so I haven't heard of SYMPUTX() yet. Thanks for the head's up on that one, I certainly don't need any leading or trailing blanks that I hadn't noticed messing up my code later on.