topic Re: SAS macro programming (uses DO loop inside a Macro function) in SAS Programming

SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:25:21 GMT

Hi all,

My problem is to break up a large data-set into smaller ones as below.

I have a data set containing about ~1000 variables. I converted each of these variable names to macro-variables called: varname1,varname2,,,varname100,...etc.

Now I need to generate smaller data-sets , each containing only 5 variables. For example, I tried the following SAS macro code but I get an error.

%macro batch_Chk(start,fin);

%do i =&start %to &fin by 5;
%let res=%evalf(&i. + 4);

	%do j =&i. %to &res. by 1;

		data words_&i.;   /* Words_i are the sub-data-sets with 5 variables each */
		set transf_vars; /* Transf_vars is the Original data */
		%put &res;       /* check res value...*/
		keep &&varname&i.- &&varname&res.;
	%end;
run;
%end;
%mend;

%batch_Chk (1,10);

SYMBOLGEN: Macro variable START resolves to 1
SYMBOLGEN: Macro variable FIN resolves to 10
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &fin by 5
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro BATCH_CHK will stop executing.

My goal is to have the 1000 variables (along with their content) broken into smaller data-sets with just 5 variables each.

I initially thought I could change the variable names to macro-variables and manipulate them inside a DO loop - but I am not getting the results I hoped for. Would really appreciate any advise or help about this problem!

Thanks very much!

Re: SAS macro programming (uses DO loop inside a Macro function)

Kurt_Bremser — Thu, 19 Oct 2017 14:27:10 GMT

Proper visual formatting will make solving your issues easier:

%macro batch_Chk(start,fin);
%do i = &start %to &fin by 5;
  %let res=%evalf(&i. + 4);
  %do j =&i. %to &res. by 1;
    data words_&i.; /* words_i is a subset data with 5 variables*/
    set transf_vars; /* transf_vars is the Original Data w/all Variables*/
    %put &res;
    %put &res;
    keep &&varname&i.- &&varname&res.;
  %end;
    run;
%mend;

Now you can immediately see that a %end is missing.

But your macro already coughs up at "by", as that is not a macro keyword.

Fixing that:

%macro batch_Chk(start,fin);
%do i = &start %to &fin %by 5;
  %let res=%evalf(&i. + 4);
  %do j =&i. %to &res. by 1;
    %put &res;
    data words_&i.; /* words_i is a subset data with 5 variables*/
    set transf_vars; /* transf_vars is the Original Data w/all Variables*/
    keep &&varname&i.- &&varname&res.;
    run;
  %end;
%end;
%mend;

Moved the %put &res; to a place where it is easier to understand, and removed the second identical statement.

But where do you get the 1000 macro variables varname1 to varname1000 from?

Re: SAS macro programming (uses DO loop inside a Macro function)

Kurt_Bremser — Thu, 19 Oct 2017 14:31:38 GMT

Ups, just saw that the inner %do loop makes no sense at all:

%macro batch_Chk(start,fin);
%do i = &start %to &fin %by 5;
  %let res=%evalf(&i. + 4);
  %put &res;
  data words_&i.; /* words_i is a subset data with 5 variables*/
  set transf_vars; /* transf_vars is the Original Data w/all Variables*/
  keep &&varname&i.- &&varname&res.;
  run;
%end;
%mend;

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:32:01 GMT

Hi Kurt,
thanks for your reply. I noticed the %END was missing myself - and then from your post I have fixed the %BY also.

But I still get an ERROR:

SYMBOLGEN: Macro variable START resolves to 1
SYMBOLGEN: Macro variable FIN resolves to 10
WARNING: Apparent invocation of macro EVALF not resolved.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable RES resolves to %evalf(1 + 4)
WARNING: Apparent invocation of macro EVALF not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &res.
ERROR: The %TO value of the %DO J loop is invalid.
ERROR: The macro BATCH_CHK will stop executing.

Re: SAS macro programming (uses DO loop inside a Macro function)

Kurt_Bremser — Thu, 19 Oct 2017 14:36:24 GMT

That's because there is no %evalf function, you probably meant %sysevalf, but that is overkill, as we only work with integer values here:

%macro batch_Chk(start,fin);
%do i = &start %to &fin %by 5;
  %let res=%eval(&i. + 4);
  %put &res;
  data words_&i.; /* words_i is a subset data with 5 variables*/
  set transf_vars; /* transf_vars is the Original Data w/all Variables*/
  keep &&varname&i.- &&varname&res.;
  run;
%end;
%mend;

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:36:41 GMT

The outer loop is supposed to handle the next group of 5 words.

The inner loop indexed by J runs from ` to 5, then the outer index I becomes 6: so that J now runs from 6 to 10.

Obviously eventually I hope to extend it to handle 1000 variables (in my dataset transf_vars).

I'm not sure how I would do the breaking down (keeping adjacent sets of 5 variables together)

Re: SAS macro programming (uses DO loop inside a Macro function)

ballardw — Thu, 19 Oct 2017 14:40:14 GMT

As posted that code will not compile the macro as there is an unclosed %do statement.

You want %by not by in the loop

Since you aren't actually using the macro variable J any where why is it there? As written it will overwrite the data set words&i 5 times (if the by issue gets fixed)

I strongly suggest that before actually manipulating your data that you just print out the index values and resolved values of the variable names.

I have a hard time seeing a good use for this operation though. Especially since most of the data sets following the logic you attempted would not have identification variables to allow easy realignment.

Tested code would require an example input data set and the macro variables varname1 to varnamennn.

And if the number of variables is not an integer multiple of 5 you'll get errors for the last "set".

Re: SAS macro programming (uses DO loop inside a Macro function)

Kurt_Bremser — Thu, 19 Oct 2017 14:43:21 GMT

@nstdt wrote:

The outer loop is supposed to handle the next group of 5 words.

The inner loop indexed by J runs from ` to 5, then the outer index I becomes 6: so that J now runs from 6 to 10.

Obviously eventually I hope to extend it to handle 1000 variables (in my dataset transf_vars).

I'm not sure how I would do the breaking down (keeping adjacent sets of 5 variables together)

You don't need the "j" loop at all.

The first iteration of the outer loop will create dataset words_1 with variables 1 to 5, the next will create words_6 with vars 6 to 10, and so on.

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:43:50 GMT

Thanks for the help!

Your version runs, though it throws an error which I should have anticipated:

SYMBOLGEN: Macro variable VARNAME5 resolves to AVGEARN_NSA
ERROR: Missing numeric suffix on a numbered variable list (AUTO_REPO_DIRECT-AVGEARN_NSA).

The problem is that though I have the macro-variables names set up as varname1, varname2,...,varname1000,

when SAS dereferences them, the ordering is lost -the actual names inside varname1 , varname2 , etc. are not similar to each other at all.

Thanks again for your help , but if you have any ideas how I go about creating these sus-data-set form the original data (actually it has exactly 726 names), please do help!

Thanks!

Re: SAS macro programming (uses DO loop inside a Macro function)

Kurt_Bremser — Thu, 19 Oct 2017 14:47:02 GMT

Now that can be solved with an inner loop:

%macro batch_Chk(start,fin);
%do i = &start %to &fin %by 5;
  %let res=%eval(&i. + 4);
  data words_&i.; /* words_i is a subset data with 5 variables*/
  set transf_vars; /* transf_vars is the Original Data w/all Variables*/
  keep
  %do j = &i %to &res;
    &&varname&j.
  %end;
  ;
  run;
%end;
%mend;

Re: SAS macro programming (uses DO loop inside a Macro function)

Reeza — Thu, 19 Oct 2017 14:47:53 GMT

Use the SASHELP.VTABLE data which includes the VARNUM so you can control the ordering of your variables.

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:49:06 GMT

You said:

You're right, the resolved names of the macro-variables do not have any oredring pattern and I get errors.

So I'm back to Square-One - How do I break down a dataset of 726 variables into smaller data-sets with just 5 variables each?

Re: SAS macro programming (uses DO loop inside a Macro function)

Reeza — Thu, 19 Oct 2017 14:54:09 GMT

Why are you doing this in the first place? If there's a valid reason, there's usually a way to organize the data that makes sense contextually. Are you trying to make a fact/dim type structure to reduce the size of the data set?

Breaking things into groups of 5 seems like a strange decision. Why not 10 or 12?

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 14:54:26 GMT

Hi Kurt - thank , this works!

I am now going to try and extend this to my original data-set, which actually has 726 variables (not 1000, and so the last loop may have some issues). Will try to work around or post back here!

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 15:03:36 GMT

Good question, and I will explain the details, hoping maybe a better way to do things might come out of it!

I have 726 econometric variables - and I need to check their Time Series Stationarity. This check is done in a macro which produces very long reports-

making it hard to read if there are lots of variables - even 5 can be hard to read, since there are a lot of output stats.

Yes, I thought about having 10 variables in each groups, but I thought it would make it hard to read.

So, I thought I would try and produce smaller data-sets, with the variables just selected from adjacent groups of 5.

I've seen SAS code where you can say "Keep A1-A5" in the data-step and was hoping for something similar.

But the underlying original variable names (once your esolve varname1,varname2 etc.) are not similar at all.

So SAS complained again, but I'm now trying to refine Kurt's latest code - I think it works , just that it makes the last data-set empty (because I have 726 rather than 1000 names - not divisible by 5).

Re: SAS macro programming (uses DO loop inside a Macro function)

Reeza — Thu, 19 Oct 2017 15:07:47 GMT

Change the macro to take another parameter - a list of variables and add the KEEP to the data step to filter the data.

Then call the macro with your variable lists.

Re: SAS macro programming (uses DO loop inside a Macro function)

gamotte — Thu, 19 Oct 2017 15:23:17 GMT

Hello,

Here is an alternative program :

%macro breakup;

	%let n=1000;
	%let n1=5;
	%let nds=%eval(&n./&n1.);

	%if %eval(&nds.*&n1.) ne &n. %then %do;
		%let nds=&nds.+1;
	%end;

	proc sql noprint;
		SELECT NAME
		INTO :varlist SEPARATED BY " "
		FROM dictionary.columns
		WHERE LIBNAME="WORK" AND MEMNAME="HAVE";
	quit;

	data
		%do i=1 %to &nds.;
		have_&i ( keep=%scan(&varlist.,&n1.*(&i.-1)+1," ") -- %scan(&varlist.,%sysfunc(min(&n1.*&i.,&n.))," ") )
		%end;
		;
		set have;
	run;
%mend;

%breakup;

If the total number of variables is not a multiple of the number of variables per dataset, you'll have

to manually create the dataset for the remaining columns.

Edit I have edited the program to handle all cases.

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 15:24:10 GMT

Hi Kurt,

I took your solution, but I have to add that that I shifted the indexes to 6 and 5 instead of 5 and 4 - since I actually have 726 variables (divisible by 6 and only one more that 5, so the final report can still be read - just barely), and not 1000.

Thanks very much again! Especially for catching all the small mistakes which I hadn't noticed despite staring at them for a long time!!!

If you think of a better way to break-up a data-set with ~1000 variables into smaller datasets, please let me know on this thread!

Re: SAS macro programming (uses DO loop inside a Macro function)

nstdt — Thu, 19 Oct 2017 15:26:24 GMT

Thanks for the alternative solution! I plan to go through it as well and understand it.

I thought it was a simple task - break down a large dataset into smaller blocks of 5-6 variables each. But the implementation

turned out harder than I thought 🙂

Re: SAS macro programming (uses DO loop inside a Macro function)

Reeza — Thu, 19 Oct 2017 15:34:20 GMT

I would second going with @gamotte solution, with a small modification, the addition of an ORDER BY statement.

	proc sql noprint;
		SELECT NAME
		INTO :varlist SEPARATED BY " "
		FROM dictionary.columns
		WHERE LIBNAME="WORK" AND MEMNAME="HAVE"
                Order by VARNUM;
	quit;