I have some information in my dataset which I have written into a macro variable. When I want to include this information in an automated email but the issue is I want one row per obs.
proc sql noprint;
select distinct i_oth
into :PMOD_LIST1 separated by " "
from work.data1;
quit;
%put NOTE: &=PMOD_LIST1;
My issue is that it looks like this;
NOTE: PMOD_LIST1=APR_19 Obs=6336566 APR_20 Obs=172 AUG_19 Obs=7308518 DEC_19 Obs=6571416 FEB_19 Obs=6201178 FEB_20 Obs=6045254
JAN_19 Obs=6707812 JAN_20
I would like it to look like this;
NOTE: PMOD_LIST1=
APR_19 Obs=6336
APR_20 Obs=12677
AUG_19 Obs=73085
DEC_19 Obs=65716
FEB_19 Obs=62018
FEB_20 Obs=5678
JAN_19 Obs=67012
JAN_20 Obs=72269
So in a sense my separator is a row. This makes it look better in an email to users. Any help would be appreciated.
Hi,
try:
proc sql noprint;
select distinct age
into :PMOD_LIST1 separated by "0a0d"x
from sashelp.class;
quit;
%put NOTE: &=PMOD_LIST1;
filename t temp;
data _null_;
file t;
put "&PMOD_LIST1.";
run;
data _null_;
infile t;
input;
put _infile_;
run;
you won't see "enters" in the log but they are "embedded" in the text (see the log)
But frankly I would do it bit different, e.g. by writing data from data set just into the email.
All the best
Bart
Thanks for this. Would you be able to describe your solution (writing the column straight into the email) with maybe an example with one of the sashelp datasets, please?
Can you describe why a macro variable is preferable to creating a data set and proc print? Or just plain select without NOPRINT for proc sql?
I don't see the need to use a macro-variable at all. You can use a simple data-step to get what you want:
filename mail ....;
proc sort data=work.data1(keep= i_oth) out=work.maildata nodupkey;
by i_oth;
run;
data _null_;
set work.maildata;
file mail;
if _n_ = 1 then do;
put "NOTE: PMOD_LIST1=";
end;
put i_oth;
run;
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.
Ready to level-up your skills? Choose your own adventure.