topic Re: PROC REPORT. obs number reported in SAS Programming

PROC REPORT. obs number reported

braam — Sat, 23 Nov 2019 16:05:03 GMT

I'm wondering why my code below starts from OBS of 2, not 1.


%let data= sashelp.cars;
%let FN= 1;
%let LN= 50;
%let column= _all_;
%let Gvar= make;
%let disp_Gvar= DISP_&Gvar.;
%let temp_Gvar= temp_&Gvar.;
%let cnt_Gvar= ctn_&Gvar.;
%let hold_Gvar= hold_&Gvar.;

	proc report data=&data. (firstobs= &FN. obs= &LN.) ;
		column obs &disp_Gvar. &column;
		define obs 			/computed;

		define &Gvar. 		/order noprint;
		define &disp_Gvar. 	/computed;

		compute obs;
			_obs + 1;
			obs = _obs;
			endcomp;

		compute before &Gvar.;
			&cnt_Gvar. + 1;
			&hold_Gvar.= &Gvar.;
			endcomp;
		compute &Gvar.;
			length sval $100 bk $20;
			if mod(&cnt_Gvar., 2) = 0 then bk= 'cxc4efd3'; else if bk= 'cxdddddd';
			sval= catt('style={background=', bk, '}');
			call define (_row_, 'style', sval);
			endcomp;
		compute &disp_Gvar. / character length= 20;
			&disp_Gvar. = &hold_Gvar.;
			endcomp;
		run;

obs DISP_make Model Type Origin DriveTrain MSRP Invoice EngineSize Cylinders Horsepower MPG_City MPG_Highway Weight Wheelbase Length

2 Acura MDX SUV Asia All $36,945 $33,337 3.5 6 265 17 23 4451 106 189

3 Acura RSX Type S 2dr Sedan Asia Front $23,820 $21,761 2 4 200 24 31 2778 101 172

4 Acura TSX 4dr Sedan Asia Front $26,990 $24,647 2.4 4 200 22 29 3230 105 183

5 Acura TL 4dr Sedan Asia Front $33,195 $30,299 3.2 6 270 20 28 3575 108 186

6 Acura 3.5 RL 4dr Sedan Asia Front $43,755 $39,014 3.5 6 225 18 24 3880 115 197

7 Acura 3.5 RL w/Navigation 4dr Sedan Asia Front $46,100 $41,100 3.5 6 225 18 24 3893 115 197

8 Acura NSX coupe 2dr manual S Sports Asia Rear $89,765 $79,978 3.2 6 290 17 24 3153 100 174

10 Audi A4 1.8T 4dr Sedan Europe Front $25,940 $23,508 1.8 4 170 22 31 3252 104 179

11 Audi A41.8T convertible 2dr Sedan Europe Front $35,940 $32,506 1.8 4 170 23 30 3638 105 180

12 Audi A4 3.0 4dr Sedan Europe Front $31,840 $28,846 3 6 220 20 28 3462 104 179

13 Audi A4 3.0 Quattro 4dr manual Sedan Europe All $33,430 $30,366 3 6 220 17 26 3583 104 179

14 Audi A4 3.0 Quattro 4dr auto Sedan Europe All $34,480 $31,388 3 6 220 18 25 3627 104 179

15 Audi A6 3.0 4dr Sedan Europe Front $36,640 $33,129 3 6 220 20 27 3561 109 192

16 Audi A6 3.0 Quattro 4dr Sedan Europe All $39,640 $35,992 3 6 220 18 25 3880 109 192

17 Audi A4 3.0 convertible 2dr Sedan Europe Front $42,490 $38,325 3 6 220 20 27 3814 105 180

18 Audi A4 3.0 Quattro convertible 2dr Sedan Europe All $44,240 $40,075 3 6 220 18 25 4013 105 180

19 Audi A6 2.7 Turbo Quattro 4dr Sedan Europe All $42,840 $38,840 2.7 6 250 18 25 3836 109 192

20 Audi A6 4.2 Quattro 4dr Sedan Europe All $49,690 $44,936 4.2 8 300 17 24 4024 109 193

21 Audi A8 L Quattro 4dr Sedan Europe All $69,190 $64,740 4.2 8 330 17 24 4399 121 204

obs	DISP_make	Model	Type	Origin	DriveTrain	MSRP	Invoice	EngineSize	Cylinders	Horsepower	MPG_City	MPG_Highway	Weight	Wheelbase	Length
2	Acura	MDX	SUV	Asia	All	$36,945	$33,337	3.5	6	265	17	23	4451	106	189
3	Acura	RSX Type S 2dr	Sedan	Asia	Front	$23,820	$21,761	2	4	200	24	31	2778	101	172
4	Acura	TSX 4dr	Sedan	Asia	Front	$26,990	$24,647	2.4	4	200	22	29	3230	105	183
5	Acura	TL 4dr	Sedan	Asia	Front	$33,195	$30,299	3.2	6	270	20	28	3575	108	186
6	Acura	3.5 RL 4dr	Sedan	Asia	Front	$43,755	$39,014	3.5	6	225	18	24	3880	115	197
7	Acura	3.5 RL w/Navigation 4dr	Sedan	Asia	Front	$46,100	$41,100	3.5	6	225	18	24	3893	115	197
8	Acura	NSX coupe 2dr manual S	Sports	Asia	Rear	$89,765	$79,978	3.2	6	290	17	24	3153	100	174
10	Audi	A4 1.8T 4dr	Sedan	Europe	Front	$25,940	$23,508	1.8	4	170	22	31	3252	104	179
11	Audi	A41.8T convertible 2dr	Sedan	Europe	Front	$35,940	$32,506	1.8	4	170	23	30	3638	105	180
12	Audi	A4 3.0 4dr	Sedan	Europe	Front	$31,840	$28,846	3	6	220	20	28	3462	104	179
13	Audi	A4 3.0 Quattro 4dr manual	Sedan	Europe	All	$33,430	$30,366	3	6	220	17	26	3583	104	179
14	Audi	A4 3.0 Quattro 4dr auto	Sedan	Europe	All	$34,480	$31,388	3	6	220	18	25	3627	104	179
15	Audi	A6 3.0 4dr	Sedan	Europe	Front	$36,640	$33,129	3	6	220	20	27	3561	109	192
16	Audi	A6 3.0 Quattro 4dr	Sedan	Europe	All	$39,640	$35,992	3	6	220	18	25	3880	109	192
17	Audi	A4 3.0 convertible 2dr	Sedan	Europe	Front	$42,490	$38,325	3	6	220	20	27	3814	105	180
18	Audi	A4 3.0 Quattro convertible 2dr	Sedan	Europe	All	$44,240	$40,075	3	6	220	18	25	4013	105	180
19	Audi	A6 2.7 Turbo Quattro 4dr	Sedan	Europe	All	$42,840	$38,840	2.7	6	250	18	25	3836	109	192
20	Audi	A6 4.2 Quattro 4dr	Sedan	Europe	All	$49,690	$44,936	4.2	8	300	17	24	4024	109	193
21	Audi	A8 L Quattro 4dr	Sedan	Europe	All	$69,190	$64,740	4.2	8	330	17	24	4399	121	204

To get what I would like to have, the below change works for me.

*WRONG;
		compute obs;
			_obs + 1;
			obs = _obs;
			endcomp;


*RIGHT;
		compute obs;
			_obs + 1;
			obs = _obs - 1;
			endcomp;

Re: PROC REPORT. obs number reported

jvdl — Sat, 23 Nov 2019 16:19:32 GMT

Try swapping:

compute obs;
	_obs + 1;
	obs = _obs;
endcomp;

For:

compute obs;
	obs = _obs;
	_obs + 1;
endcomp;

Re: PROC REPORT. obs number reported

braam — Sat, 23 Nov 2019 16:55:46 GMT

@jvdl
Thanks! That works.

One more related question: In such a code, Is 1 initially assigned to _obs? Because its initial value hasn't been given.

Re: PROC REPORT. obs number reported

braam — Sat, 23 Nov 2019 17:00:34 GMT

Just noticed a weird pattern in the column of obs:

8 and 28 are missing.

Could anybody help me with that?

Re: PROC REPORT. obs number reported

Cynthia_sas — Sat, 23 Nov 2019 17:25:18 GMT

Hi:

Keep in mind that the "fake" obs number you are computing will NOT necessarily match the obs number that PROC PRINT would show.

Why are you overcomplicating this by using a macro. The best practice is to get the program working for 1 set of hardcoded variables and THEN to make the program more general by adding macro logic. It seems like you did NOT start with a working program.

Cynthia

Re: PROC REPORT. obs number reported

Cynthia_sas — Sat, 23 Nov 2019 17:47:37 GMT

Hi:

The reason you are getting "odd" calculated values for your fake obs numbers is because PROC REPORT is calculating your value on EVERY row it writes and EVERY break. So you have a header row, that is row #1 and technically, the first data row on the report is absolute row #2. And, PROC REPORT is doing something at the break for MAKE so that is throwing off your COMPUTE block.

PROC REPORT does NOT use OBS numbers like PROC PRINT. If you MUST have obs numbers, then just create them in a DATA step, they will be more accurate. Before you jump straight to macro code, always start with a working program and then macro-ize it. So I would recommend making sure the OBS column is right before macro-izing. This worked for me to make sure I had a working SAS program -- I used a DATA step to capture _n_ and in a production program, I would sort sashelp.cars first:

proc sort data=sashelp.cars out=cars_make;
  by make;
run;
   
data finalcars;
   set cars_make;
      ** capture internal obs number into variable for reporting;
      ** after ensuring that data is correctly ordered by Make;
     _obs = _n_;
run;
 
** using firstobs and obs does not make sense if you are wanting to "group" by make;
** but I kept that part in the report definition;
proc report data=finalcars (firstobs= 1 obs= 50) ;
   column make _obs DISP_make _all_;
   define make /order noprint;
   define _obs /order;
   define DISP_make /computed;
   compute before make;
      cnt_make + 1;
      hold_make= make;
   endcomp;
   compute make;
      length sval $100 bk $20;
      if mod(cnt_make, 2) = 0 then bk= 'cxc4efd3'; 
      else bk= 'cxdddddd';
      sval= catt('style={background=', bk, '}');
      call define (_row_, 'style', sval);
   endcomp;
   compute DISP_make / character length= 20;
      DISP_make = hold_make;
   endcomp;
run;

Cynthia

Re: PROC REPORT. obs number reported

braam — Sat, 23 Nov 2019 19:15:54 GMT

@cynthia

Thanks a lot for your kind explanation. I would follow your suggestion.

Regarding FIRSTOBS and OBS, I am having them because I just would like to see the first 50 observations. I hope that they do not raise up any issues. (Well, PROC REPORT is still strange to me...)

Re: PROC REPORT. obs number reported

Cynthia_sas — Sat, 23 Nov 2019 23:10:43 GMT

Hi:
FIRSTOBS and OBS work at the data set level, returning rows BEFORE they go to PROC REPORT. Trying to duplicate the internal observation number with PROC REPORT isn't something I would recommend trying in a COMPUTE block for the reasons previously mentioned.
Cynthia