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
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?
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. 😞
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;
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'. 😉
I would just add the following to your call to proc tabulate:
PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1 (drop=_:);
Art, CEO, AnalystFinder.com
I think this will still leave the leading "," in the output, but maybe that's fine for the OP.
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!
Don't include the comma in the PUT statement. Tell the FILE statement to generate the commas.
file .... dsd ;
....
put (_all_) (+0) ;
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!
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.