09-14-2016 09:56 AM - edited 09-15-2016 04:02 AM
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!
09-14-2016 10:20 AM
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?
09-14-2016 10:25 AM
09-15-2016 06:21 AM - edited 09-15-2016 06:25 AM
The code i am using for the email section is as follows:
select count('JobID'n) into :Count from WORK.Email;
%if &count>0 %then %do;
%put execute further;
%macro email(name=, EMAIL=, M=, CODE=, Id=, Total=,);
filename mymail email from=""
put //"Hello &NAME,"
/ "Please find below the total for &Id"
call execute('%email(name='||R||', email="'||Email||'", M='||M||', Id='||ID||', CODE='||code||', Total='||Total||')');
%else %put exit;
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 '|'?
09-15-2016 06:37 AM
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.
09-15-2016 06:51 AM
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.
09-15-2016 07:56 AM
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.
09-15-2016 08:05 AM
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.