BookmarkSubscribeRSS Feed
Prajeesh
Fluorite | Level 6

Hi,

 

DATA _NULL_;                                             
  FILENAME MYFILE EMAIL                                  
TO=(                                                      
      XXXXXXXX                
     /*                                                   
            
                         by Prajeesh Karat.              
      */                                                 
     )                                                   
  CC=(                                                    
      XXXX                
     )                                                   
  FROM=(                                                  
      XXXXXXXX                    

 

  ATTACH=('Mainframe PS file with 4 columns'                      
NAME='Daily status Report' CONTENT_TYPE="TEXT/PLAIN"   
   EXTENSION='CSV');                                    
                                        
  FILE MYFILE;                         
  IF _N_ = 1 THEN DO;                                  

PUT "!EM_SUBJECT!" "Testin";

end;

 

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

data _null_;

  infile "<yourfile>.txt";

  file "<processed>.csv";

  put(tranwrd(_infile_,' ',','));

run;

(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.

Prajeesh
Fluorite | Level 6

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.. 🙂

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Prajeesh
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

data _null_;

  infile "<yourfile>.txt";

  file "<processed>.csv";

  put(tranwrd(_infile_,' ',','));

run;

 

Which would run before you get onto your email step.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2818 views
  • 0 likes
  • 2 in conversation