topic Re: strip function not removing trailing spaces. in SAS Programming

strip function not removing trailing spaces.

HitmonTran — Mon, 14 Jun 2021 04:28:43 GMT

Hi,

I'm using proc sql to create a macro of the counts per treatment group. However, when &trt1 and &trt2 resolves, there's a trailing space I cannot get rid of

Code:

proc sql;
	    	select (strip(put(count(distinct usubjid), best.))) into :trt1 from adam.adce where (TRT01AN=1);
			select (strip(put(count(distinct usubjid), best.))) into :trt2 from adam.adce where (TRT01AN=2);
			select (strip(put(count(distinct usubjid), best.))) into :trt3 from adam.adce where (TRT01AN=3);
quit;
	
%put &trt1 &trt2 &trt3;	

proc report data=final split='@';
	columns    ("&ALBL1.@(N=&trt1.)" group1 )   ("&PLBL1.@(N=&trt2.)" group2)  
				define group1 / "Any Grade." style(column)=[just=center];
				define group2/ "Grade 3+"   style(column)=[just=center];
run;

Re: strip function not removing trailing spaces.

jimbarbour — Mon, 14 Jun 2021 04:37:53 GMT

I would perform the STRIP() after the SQL completes. The use of the : in an SQL statement to place a column into a macro variable invariably adds spaces which are a pain in the, um, posterior.

You could code after the SQL but before the Proc Report:

%LET trt1 = %SYSFUNC(STRIP(&trt1));
%LET trt2 = %SYSFUNC(STRIP(&trt2));
%LET trt3 = %SYSFUNC(STRIP(&trt3));

Jim

Re: strip function not removing trailing spaces.

Ksharp — Mon, 14 Jun 2021 10:28:59 GMT

proc sql;
select (strip(put(count(distinct usubjid), best.))) into :trt1 separated by ' ' from adam.adce where (TRT01AN=1);

Re: strip function not removing trailing spaces.

Tom — Mon, 14 Jun 2021 13:13:09 GMT

Use the TRIMMED keyword in SAS to have it "strip" the values being written into the macro variables.

proc sql;
  select count(distinct usubjid) into :trt1 trimmed from adam.adce where (TRT01AN=1);
  select count(distinct usubjid) into :trt2 trimmed from adam.adce where (TRT01AN=2);
  select count(distinct usubjid) into :trt3 trimmed from adam.adce where (TRT01AN=2);
quit;

If you are sure you always have at least one record per TRT01AN you could simplify

proc sql;
  select count(distinct usubjid) into :trt1-
  from adam.adce where (TRT01AN in (1 2 3))
  group by TRT01AN
  order by TRT01AN
;
quit;