The SAS Output Delivery System and reporting techniques

How to seperate columns in Mainframe PS file to differnt columns in CSV files

Reply
New Contributor
Posts: 3

How to seperate columns in Mainframe PS file to differnt columns in CSV files

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to seperate columns in Mainframe PS file to differnt columns in CSV files

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.

New Contributor
Posts: 3

Re: How to seperate columns in Mainframe PS file to differnt columns in CSV files

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.. Smiley Happy

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to seperate columns in Mainframe PS file to differnt columns in CSV files

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. 

New Contributor
Posts: 3

Re: How to seperate columns in Mainframe PS file to differnt columns in CSV files

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?

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: How to seperate columns in Mainframe PS file to differnt columns in CSV files

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.

Ask a Question
Discussion stats
  • 5 replies
  • 354 views
  • 0 likes
  • 2 in conversation