BookmarkSubscribeRSS Feed
DKidd
Fluorite | Level 6

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                                       

9 REPLIES 9
collinelliot
Barite | Level 11

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?

DKidd
Fluorite | Level 6

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

collinelliot
Barite | Level 11

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;
DKidd
Fluorite | Level 6

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'.  😉

art297
Opal | Level 21

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

 

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

Art, CEO, AnalystFinder.com

 

collinelliot
Barite | Level 11

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

DKidd
Fluorite | Level 6

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!

Tom
Super User Tom
Super User

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

file .... dsd ;
....
put (_all_) (+0) ;
DKidd
Fluorite | Level 6

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!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1091 views
  • 3 likes
  • 4 in conversation