Code to split data into lines for email use...

[MattehWoo]

Hi all,

I need some help with my SAS project...

I have the following data in a column (but much longer):

1 - 2011 - 29FEB2016 | 1 - 2011 - 30NOV2015 | 1 - 2011 - 29MAY2015 | 1 - 2011 - 27FEB2015 |

The reason i have it all like this in one column is because i need to send it via email so that it can be copied and pasted into excel.

Now in order to do that properly i need SAS to split this data into lines when it puts it into email form so that it looks more like:

1 - 2011 - 29FEB2016 

1 - 2011 - 30NOV2015 

1 - 2011 - 29MAY2015 

1 - 2011 - 27FEB2015 

Is there a simple way to do this?

Any help would be much appreciated!

[RW9]

My first question is this, do you believe that the best way to approach this is to compile your data to one column, push it out through an open email system, then have someone at the other end copy and paste this into Excel?  I really don't think it is.  To shrink the effort for minimal change you could just export your data to a CSV file, and attach that to a mail - CSV can be opened directly in Excel so saves that copy and pasting and the conatenation of data.  Or you could output an Excel file directly.

Without know what this is for, its a bit tricky to say, but this information is held somewhere, can your recipient not access this directly - programmatically, to avoid the whole mailing bit?

[Cynthia_sas]

Hi:
Why does the data have to be copied and pasted into Excel. You can create a CSV file with SAS or an XLSX file (if you have SAS 9.4) and then just attach the file to your email.

Since you did not provide code or anything other than a snippet of your example data, it is hard to provide constructive suggestions for your question. You say that you have the "whole project" working and ready. Does that mean you are successfully emailing something? What is the something you are emailing? How do you do the email? What is the code you are using or what is the process you are using? Are you sending a SAS dataset in email? Or, do you have the string you show in the body of the email? How do you get the data in the body of the email? Are you using a DATA step program?

It would help us help you if you could show more of your code, including the code you are using for e-mailing and could explain what you are sending.

Splitting a text string like you show at the pipe symbol ( | ) is fairly easy if you use the SCAN function in a loop. However, without knowing more about your process or existing program, it is hard to suggest where to use the SCAN function.

cynthia

[MattehWoo]

Hi,

The code i am using for the email section is as follows:

/*%macro execute;
proc sql;
select count('JobID'n) into :Count from WORK.Email;
quit;
%if &count>0 %then %do;
%put execute further;

%macro email(name=, EMAIL=, M=, CODE=, Id=, Total=,);
filename mymail email from=""
sender=""
CC=""
SUBJECT= "&Id"
to=(&email);
data _null_;
file mymail;
put //"Hello &NAME,"
/" "
/ "Please find below the total for &Id"
/" "
/"&total"
/" "
/"Thank you,"
/" "
/"";
run;
%mend email;

data _null_;
set work.EMAIL;
call execute('%email(name='||R||', email="'||Email||'", M='||M||', Id='||ID||', CODE='||code||', Total='||Total||')');
run;

%end;
%else %put exit;
%mend;
%execute;

The email will then show the data as it is in the column:

1 - 2011 - 29FEB2016 | 1 - 2011 - 30NOV2015 | 1 - 2011 - 29MAY2015 | 1 - 2011 - 27FEB2015 |.

The person the recieves the email will then copy this data and put it into a tool. However the tool requires each bit of data to be on seperate lines.

Is there a way that when the code calls the &total it can seperate the different parts of data where i've put the '|'?

[RW9]

In this example I have replaced the dataset work.email (as I don't have that) with a fixed string to emulate it (and I put out to a text file, but email should be exactly the same):

data _null_;
/*  set work.email;*/
  call execute('filename mymail "c:\temp.txt";');
  call execute('data _null_; file mymail; put "Hello,";');
  do i=1 to countw("1 - 2011 - 29FEB2016 | 1 - 2011 - 30NOV2015 | 1 - 2011 - 29MAY2015 | 1 - 2011 - 27FEB2015","|");
    call execute(cat('put "',scan("1 - 2011 - 29FEB2016 | 1 - 2011 - 30NOV2015 | 1 - 2011 - 29MAY2015 | 1 - 2011 - 27FEB2015",i,"|"),'";'));
  end;
  call execute('run;');
run;

Note that there is no need for macro, or looping - the dataset base is the loop.

[MattehWoo]

Ah i see.

If the data was to change on each occasion i run the project, i'd have to change the code?

[RW9]

Sorry, I do not understand, how would the data be changing on each occasion?  And if it does, how would you plan on programming it using any syntax?  You have to have some sort of fixed base to work from otherwise any programming will change per changes to the base.

[MattehWoo]

The idea behind the program is to run it every day which will bring out a new set of totals on each run. So every day i run it will have different data in the cell.

[RW9]

If its just in the data cell then the code does not need to change.  What the above does is to loop over the data in that cell split by |, and for each one generates a codeline "put" after execution of the data _null_.  Look at the log, you can see what is happening.  if there are more elements then there will be more put statements created, if the data changes then the generated code will change.