topic Re: Loop user-defined formats in SAS Data Management

Loop user-defined formats

PeterChen — Mon, 31 Aug 2020 18:18:56 GMT

I want to loop through user-defined formats like below:

Formats:

proc format;
value cutstest_fmt
0 = '0'
1 - 199 = '001 to 199'
200 - 998 = '200 to 998'
999 = '999 to 999'
;

value cuts1_fmt
0 = '0'
1 - 349 = '001 to 349'
350 - 998 = '350 to 998'
999 = '999 to 999'
;
run;

Loop:

DO i=1 to formats._NAME_;
proc freq data = dt;
	tables col1* col2/MISSING norow nocol nopercent;
	format col1 i.;
run;
end;

I got errors. I think I miss something here. Any help?

Explain more here:

The above codes are incorrect. My question is how can I loop through user-defined formats value names.

I just take proc freq here for example that I want to loop formats in this proc freq instead of copy/paste proc freq multiple times and change the formats names.

Re: Loop user-defined formats

Reeza — Mon, 31 Aug 2020 17:59:56 GMT

That's not how you loop a proc in SAS. What are you trying to do overall? You likely need macros if you want a loop but if it's only two that's not what I would recommend. There are also Multilevel formats (MLF) which may be more appropriate here depending on what you're trying to accomplish.

Re: Loop user-defined formats

PeterChen — Mon, 31 Aug 2020 18:03:27 GMT

Actually this loop is in a large macro.
And the formats will also be used in another place. I just use proc freq here for asking question about how to loop through multiple different formats on variable col1.

Re: Loop user-defined formats

ballardw — Mon, 31 Aug 2020 18:05:00 GMT

Copy the LOG and paste into a code box opened on the forum with the </> icon.

That way we can organize a response in order to your errors.

It appears that you may have been attempting to use the macro language constructs but without any macro coding.

What value would you expect "formats._name_" to have in the following code and where would the value come from?

DO i=1 to formats._NAME_;

Since "i" would be numeric then you would be using a format 1. in the first iteration of

format col1 i.;

if the "i" resolved at all, which it can't currently.

Re: Loop user-defined formats

PeterChen — Mon, 31 Aug 2020 18:08:46 GMT

My code is wrong. I just want to explain the question I face...
There is no formats._NAME_. I just want to loop the formats value name in the proc freq once instead of typing/coping multiple times and change the formats name.

Re: Loop user-defined formats

Tom — Mon, 31 Aug 2020 18:48:16 GMT

@PeterChen wrote:
My code is wrong. I just want to explain the question I face...
There is no formats._NAME_. I just want to loop the formats value name in the proc freq once instead of typing/coping multiple times and change the formats name.

To loop over a list of values place the list into a macro variable and use %SCAN(). You can use %SYSFUNC(COUNTW()) to find out how many items are in the list.

%let fmt_list=fmta. fmtb. ;
%do i=1 %to %sysfunc(countw(&fmt_list,%str( )));
  %let fmt=%scan(&fmt_list,&i,%str( ));
   .... format x &fmt. ...
%end;

Re: Loop user-defined formats

Astounding — Mon, 31 Aug 2020 19:08:05 GMT

@Tom has the right idea here. Because you have a larger problem than you can post, here's a link to more information that shows a few variations on this technique.

https://blogs.sas.com/content/sastraining/2015/01/30/sas-authors-tip-getting-the-macro-language-to-perform-a-do-loop-over-a-list-of-values/

Re: Loop user-defined formats

PeterChen — Mon, 31 Aug 2020 19:17:39 GMT

@Tom wrote:
@PeterChen wrote:
My code is wrong. I just want to explain the question I face...
There is no formats._NAME_. I just want to loop the formats value name in the proc freq once instead of typing/coping multiple times and change the formats name.
To loop over a list of values place the list into a macro variable and use %SCAN(). You can use %SYSFUNC(COUNTW()) to find out how many items are in the list.
%let fmt_list=fmta. fmtb. ;
%do i=1 %to %sysfunc(countw(&fmt_list,%str( )));
  %let fmt=%scan(&fmt_list,&i,%str( ));
   .... format x &fmt. ...
%end;

proc catalog catalog=formats;
contents out=fmtcontents(keep=name);
quit;

proc sql noprint;
select name into :fmtnames
from fmtcontents;
quit;

%let fmt_list=&fmtnames. ;
%do i=1 %to %sysfunc(countw(&fmt_list,%str( )));
  %let fmt=%scan(&fmt_list,&i,%str( ));
  proc freq data = data;
    tables col1 * col2/MISSING norow nocol nopercent;
    format col1 &fmt.;
  run;
%end;

I put the unique format names into macro variable fmtnames but got incorrect result:

The col1 did not format...

Re: Loop user-defined formats

Reeza — Mon, 31 Aug 2020 19:21:46 GMT

Make sure to test every step of your code. The following step will only return a single format, not the list because you don't have a separated by. If you check how that variable resolves you'll see that.

proc sql noprint;
select name into :fmtnames
from fmtcontents;
quit;

%put &fmtnames;

Try this instead:

proc sql noprint;
select name into :fmtnames separated by " "
from fmtcontents;
quit;

Re: Loop user-defined formats

Tom — Mon, 31 Aug 2020 19:25:26 GMT

Make sure to generate valid code. A format specification needs a period. Like this:

format varname fmtname. ;

A statement like this:

format varname fmtname ;

Will remove any attached formats from the two variable names listed. If the variable fmtname does not exist you will get a note/warning/error in the log.

Re: Loop user-defined formats

PeterChen — Mon, 31 Aug 2020 19:29:14 GMT

@Tom wrote:
@PeterChen wrote:
My code is wrong. I just want to explain the question I face...
There is no formats._NAME_. I just want to loop the formats value name in the proc freq once instead of typing/coping multiple times and change the formats name.
To loop over a list of values place the list into a macro variable and use %SCAN(). You can use %SYSFUNC(COUNTW()) to find out how many items are in the list.
%let fmt_list=fmta. fmtb. ;
%do i=1 %to %sysfunc(countw(&fmt_list,%str( )));
  %let fmt=%scan(&fmt_list,&i,%str( ));
   .... format x &fmt. ...
%end;

This is correct answer.
I tested it and only one thing to mention: remember to add one more "." after "&fmt."

%macro test;
%do i=1 %to %sysfunc(countw(&fmtnames.,%str( )));
   %let fmt = %scan(&fmtnames., &i, %str( ));
  proc freq data = dt;
	tables col1 * col2/MISSING norow nocol nopercent;
	format col1 &fmt..;
  run;
%end;
%mend test;
%test;

Re: Loop user-defined formats

Tom — Mon, 31 Aug 2020 19:40:24 GMT

If the list already has the periods in the format names then your change will generate invalid code. So if you want to loop over:

%let fmt_list = best12.3;

you will end up generating code like:

format xxx best12.3. ;

If you want to make the loop smart enough to handle lists that might just have the format name instead of a full format specification then have it check whether the word in the list has the period or not. Then it will be more forgiving of lists that don't quite have the right types of values.

%do i=1 %to %sysfunc(countw(&fmt_list,%str( )));
  %let fmt=%scan(&fmt_list,&i,%str( ));
  %if not %index(&fmt,.) %then %let fmt=&fmt..;
   
%end;

Re: Loop user-defined formats

TomKari — Mon, 31 Aug 2020 22:12:36 GMT

Hi, @PeterChen

SAS has a really nifty feature called multilabel formats. If it's the kind of thing that can help solve your problem, it could be REALLY useful.

Tom

proc format;
	value cutstest_all_fmt (multilabel)
		0 = '0'
		1 - 199 = '001 to 199'
		1 - 349 = '001 to 349'
		200 - 998 = '200 to 998'
		350 - 998 = '350 to 998'
		999 = '999 to 999'
	;

data have;
	input col1 col2;
	cards;
998 160
0 473
998 473
0 160
350 473
200 110
200 38
998 38
350 110
200 110
0 39
998 39
350 160
0 175
1 308
200 473
1 160
1 1
0 473
349 308
199 39
350 1
199 308
0 38
999 38
998 39
349 308
200 39
998 473
998 160
1 473
1 1
run;

proc summary data=have nway noprint;
	class col1 / mlf;
	class col2;
	output out=work.results;
	format col1 cutstest_all_fmt.;
run;