Help using Base SAS procedures

How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Reply
Occasional Contributor
Posts: 9

How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

So, first off I'll admit that I am a user with no formal SAS training.  So everything I know about SAS (which isn't much as I look through the manuals) is through the school of hard knocks or figuring things out from other jobs.  So be kind, please?  ;-)

 

Anyways...my issue is that a user wants a total, from a transaction file, for one particular type of a transaction.  So, I used PROC  TABULATE to come up with a report that gives her what she wanted.  But, she would like the data in a CSV file.  So, looking through the forums I found a way to do it, but (just so it looks better) I would like to get rid of some fields.

So, my JCL looks like this:

//STEP1    EXEC SAS9,TIME=120,OPTIONS='LS=133,NODATE,MACRO'   
//OUT  DD DSN=<Dataset name>,DISP=SHR 

//SYSIN DD *
%GLOBAL DAT;  

.

CALL SYMPUT('DAT',PUT(DATE(),WORDDATE18.));

.

.

PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1;      
BY FLD1;                                    
CLASS FLD2;                                 
VAR TOTFEE;                                   
  FORMAT FLD1 $FLD1. FLD2 $FLD2.;     
 TABLES FLD2 ALL='TOTAL',TOTFEE;            
TITLE1 'XXXXXXXXXXXXXX';     
TITLE2 'YYYYYYYYYY';          
TITLE3 'ZZZZZZZZZZ';    
TITLE5 "&DAT";                                
 KEYLABEL N=' ';                              
*;                                            
DATA _NULL_;                                  
  FILE OUT;                                   
  SET DAT;                                    
  PUT (_ALL_) (',');                          
/*

Here's part of the output (I apologize for the cut/paste, the format got all messed up):

------------------ FLD1=AAA ------------------
                                               
 ----------------------------------------------
 |                                           |   TOTFEE|
 |                                           |---------------|
 |                                           |    SUM     |
 |-------------------------------+------------------|
 |FLD2                                  |                 |
 |-------------------------------      |                 |
 |XXXXX                               |        $0.00|
 |-------------------------------+------------------|
 |YYYYYYY                          |    $282.00|
 |-------------------------------+------------------|
 |ZZZZZZZ                           |      $53.50|

 

So, the above works great, EXCEPT I have values in my file that I have no idea where they came from, what they are and how to get rid of them, which is where my question comes in.   

 

My file looks like this:

,AAA ,AXXXX ,1 ,1 ,1 ,0       
,AAA ,BTTTTTT ,1 ,1 ,1 ,282   
,AAA ,BUUUUUU ,1 ,1 ,1 ,53.5  
,AAA ,BXXXXX ,1 ,1 ,1 ,23.5   
,AAA ,BYYYY ,1 ,1 ,1 ,820.1       

So, I have, I guess, an empty first field, the next two fields and the last fields are the ones that I want.  The three 1s, I don't want, but don't know how to get rid of them. 

My JCL output doesn't tell me anything either, other than there were 6 fields output (so maybe I don't have an empty first field).  

  

NOTE: THERE WERE 213 OBSERVATIONS READ FROM THE DATA SET WORK.RGDATA.
NOTE: THE DATA SET WORK.DAT1 HAS 22 OBSERVATIONS AND 6 VARIABLES.    
NOTE: THE PROCEDURE TABULATE PRINTED PAGES 6-7.                      
NOTE: THE PROCEDURE TABULATE USED THE FOLLOWING RESOURCES:     

 

Any help would be greatly appreciated.  Thanks                                       

PROC Star
Posts: 308

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

It's not clear to me what you want the output to look like, but have you looked at the data set created by proc tabulate to see which variables you are outputting with your _all_? I think what you're seeing is the _page_ and _table_ variables. Maybe use the put on the just the variables you want to output. Or maybe use ods csv with a proc print noobs and list only the variables you want to output?

Occasional Contributor
Posts: 9

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Posted in reply to collinelliot

Essentially I want every but those three 1s in the file.

 

I tried using the proc print (because I found that solution), but I could never get it to work.  I assume that I just missed a parameter or had the syntax wrong.  :-(

PROC Star
Posts: 308

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

The data are different, but maybe this helps?

 

proc sort data = sashelp.class out = class;
    by sex;
run;

proc tabulate data = class format = comma10.2 out = tblData;
    by sex;
    class age;
    var height;
    tables age all, height;
run;

filename out "....\do not want.txt";
DATA _NULL_;                                  
  FILE OUT;                                   
  SET tblData;                                    
  PUT (_ALL_) (',');  
run;


filename out "....\do want 1.txt";
DATA _NULL_;                                  
  FILE OUT;                                   
  SET tblData;                                    
  PUT sex ',' age ',' height_sum;
run;


ods csv file = "...\do want 2.txt";

proc print data = tblData noobs;
    var sex age height_sum;
run;

ods csv close;
Occasional Contributor
Posts: 9

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Posted in reply to collinelliot

I'll give that a shot, also, and see what I can get.  I just want to thank all of you for helping this 'noob'.  ;-)

PROC Star
Posts: 7,492

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

I would just add the following to your call to proc tabulate:

 

PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1 (drop=_:);  

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 308

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

I think this will still leave the leading "," in the output, but maybe that's fine for the OP.

Occasional Contributor
Posts: 9

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Thank you very much!  I put that in there and it worked!  I figured it would be something like that, I just didn't know what to put in the drop statement.

 

Yes, it still left that leading comma, but I can live with that if I have to.

 

Again, thanks!

Super User
Super User
Posts: 7,083

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Don't include the comma in the PUT statement. Tell the FILE statement to generate the commas.

file .... dsd ;
....
put (_all_) (+0) ;
Occasional Contributor
Posts: 9

Re: How do I remove fields of a CSV file on the mainframe created by a PROC TABULATE?

Beautiful!

So, I guess I'm going to have to try and find in my manuals what the DSD parameter along with the (+0) parameter does.

 

thanks!

Ask a Question
Discussion stats
  • 9 replies
  • 175 views
  • 3 likes
  • 4 in conversation