BookmarkSubscribeRSS Feed
Sean_OConnor
Obsidian | Level 7

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.

4 REPLIES 4
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Sean_OConnor
Obsidian | Level 7

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? 

ballardw
Super User

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?

 

andreas_lds
Jade | Level 19

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 872 views
  • 0 likes
  • 4 in conversation