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;
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
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
proc sql;
select (strip(put(count(distinct usubjid), best.))) into :trt1 separated by ' ' from adam.adce where (TRT01AN=1);
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.