topic Re: can a computed variable be made to span rows in proc report? in SAS Programming

can a computed variable be made to span rows in proc report?

jtcowder — Tue, 18 Aug 2020 19:49:17 GMT

I have a dataset with identifiers that I do not want in the final report.

For example, it is generated from this code:

data one;
	array idarr{10} $ idarr1-idarr10 ('a','b','c','d','e','f','g','h','i','j');
	do a=1 to 10;
		iden=idarr[a];
		do b=1 to 3;
			do c=1 to 2;
				output;
			end;
		end;
	end;
	drop a;
run;

Using the following, the variables span as I would like

proc report data=one spanrows;
	columns iden b c;
	define iden	/ 'id' order order=internal;
	define b	/ 'B' order order=internal;
	define c	/ 'C' order order=internal;
run;

I do not want the "iden" variable to be in the report, but want a counter [that increments with each change of iden] instead, I wrote the following:

proc report data=one spanrows;
	columns iden nobs3 b c;
	define iden		/ 'id' order order=internal;
	define nobs3	/ 'id obs 3' computed;
	define b		/ 'B' order order=internal;
	define c		/ 'C' order order=internal;

	compute nobs3;
		if not missing(iden) then dsobs3+1;
		nobs3=dsobs3;	
	endcomp;
run;

the problem is nobs3 is repeated for every row.

Is there a way to make the computed variable nobs3 span in the same way iden does?

One way I know to make it work is to create nobs3 in the dataset that is being passed to report, but, if possible, I was hoping to create it within report.

Thanks

Re: can a computed variable be made to span rows in proc report?

ChrisNZ — Tue, 18 Aug 2020 20:09:17 GMT

Why not use a format?

proc sql;
  create table _F as
  select START, monotonic() as LABEL, '$iden' as FMTNAME, 'C' as TYPE
  from (select unique IDEN as START from ONE);
proc format cntlin=_F; run;

proc report data=ONE spanrows;
	columns IDEN B C;
	define IDEN / 'id' order order=internal format=$iden.;
	define B	/ 'B' order order=internal;
	define C	/ 'C' order order=internal;
run;

Re: can a computed variable be made to span rows in proc report?

jtcowder — Tue, 18 Aug 2020 20:20:34 GMT

That certainly works, I would not have thought of that.

Thank you for the suggestion, it is much appreciated.

Re: can a computed variable be made to span rows in proc report?

novinosrin — Tue, 18 Aug 2020 20:55:42 GMT

Sir @ChrisNZ priceless solution. Jealous of how you think. I don't like you sorry.lol. 🙂 Brings me to ground zero and tears that kind of thought/application would ever strike my mind. Well done!

Re: can a computed variable be made to span rows in proc report?

ChrisNZ — Wed, 19 Aug 2020 01:14:13 GMT

@novinosrin Thank you so very much. The check is in the mail 🙂