10-05-2016 05:16 AM
FILENAME MYFILE EMAIL
by Prajeesh Karat.
ATTACH=('Mainframe PS file with 4 columns'
NAME='Daily status Report' CONTENT_TYPE="TEXT/PLAIN"
IF _N_ = 1 THEN DO;
PUT "!EM_SUBJECT!" "Testin";
Now the CSV is getting generated but all the data is coming in column.
I have a Mainframe PS file with 4 columns.. How will i seperate CSV file with seperate columns for each column that is present in Mainframe PS file
10-05-2016 05:38 AM
Sorry, your post is very confusing. What are you trying to do, post example test data. The code you have posted is creating an email and attaching a file, it makes no reference to importing it or parsing it? If its not a CSV file as you have said, then why specify extension as CSV, that doesn't make sense. What will happen is CSV is a filetype associated with Excel in a lot of cases, so Excel will try to open it, and as there are no commas, put all the data in one column. What is the delimiter from the file, if you know that then a simple step:
(note not tested) will read the file with one space delimiter, and write to the new file with a comma. You can then attach the CSV.
10-05-2016 05:52 AM
Apologies for the confusion. I am trying SAS based emailing for the first time. Let me explain in detail.
I have created a Mainframe File with 4 columns of fixed length. Idea is to mail this Mainframe to recipients in CSV format.
My requirement: Each column must get copied to one coulmn in the CSV/XLS. For e.x: Name column in Mainframe file should be copied to first column of CSV, sex column in Mainframe file to the second column of CSV.
And each of the columns should also have corresponding title in CSV file.
Currently all of the date i.e" Name, Sex is getting populated to one column in the CSV file.
The Mainframe file has the column heading along with the data as shown below
Name Age Sex
ABCF 23 m
FERT 43 F
So, Name should be copied to one column, Age to the second one and so on...
Hope i am clear now..
10-05-2016 07:35 AM
Yes, I think you have some misunderstandings there. CSV doesn't have "columns" as such, it is a text based format, with data separated by commas. What you are talking about is your recipients are opening this file in Excel, and that application is processing the csv data to put each block of data in a column.
So what you have to do is to change the text file you have, to the text file you need. As I posted above, you would read in the file from the mainframe, process the string to replace the mainframe's delimiter with a comma, then write that out to a new file. Then you would attach the new file to your email. There is no magic conversion process.
That being said there are other options, print your data to PDF or RTF would be my first choice then you can have lots of nice formatting and tables. Or read in the mainframe using infile and proc report the data to ods tagsets.excelxp.
Another option is to teach your recipients how to use Excel to read in files with different delimiters. Not sure what your delimiter is, maybe tab, if you import text from file in Excel and stipulate the delimiter is tab then Excel will process the file you have correctly, however this is a manual step by the recipient as Excel wont read it directly.
10-05-2016 07:49 AM
Thanks for your input. I had tried , EXT=PDF but the file doesn't open in the mail. Will try RTF and proc report.
I am still learning SAS, so would take some time. Do i need to give any extra inputs while giving the ext=PDF?
10-05-2016 08:51 AM
Sorry, no, extension parameter is not the one to change. Its a separate step. A file extension just tells the operating system what application to open the file with, each file can be of different formats. PDF file format is different to CSV, which is different to actual Office files, etc. You need to actually change the file you have to the file you want using SAS statements. I would suggest to learn about the system you are using before jumping into end processes.
I give you the basic step here:
Which would run before you get onto your email step.