topic Re: Listing contents of a column in an automated SAS email. in SAS Programming

Listing contents of a column in an automated SAS email.

Sean_OConnor — Tue, 14 Apr 2020 15:55:52 GMT

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.

Re: Listing contents of a column in an automated SAS email.

yabwon — Tue, 14 Apr 2020 17:02:07 GMT

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

Re: Listing contents of a column in an automated SAS email.

ballardw — Tue, 14 Apr 2020 17:06:29 GMT

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?

Re: Listing contents of a column in an automated SAS email.

Sean_OConnor — Wed, 15 Apr 2020 08:37:34 GMT

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?

Re: Listing contents of a column in an automated SAS email.

andreas_lds — Wed, 15 Apr 2020 09:25:06 GMT

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;