PROC REPORT to CSV with values in all columns in each row

[TimH]

I need to write a CSV file from PROC REPORT output, but this CSV is intended to be loaded into a relational table.  For that reason, I need each of the variables to be populated in every row of the CSV, whether they are grouping variables or not.  The code is below.  I have the OUT= because I was going to investigate the output to see if it was better to wrap the ODS code around a PROC PRINT of that dataset but I thought I'd ask here first.  This code works fine, but the grouping variables in the CSV are in 'outline' form (the value is only populated when it changes) and that won't work for loading into a table. Is there something I can add to make them displayed on every row?

ODS TAGSETS.CSVALL                                         
           PATH="pathname"                               
           file="filenname.csv";                                          
TITLE;                                                     
PROC REPORT DATA=SUM2 OUT=MYRPT;                           
  BY WEEKBEG WEEKEND;                                      
  COLUMN WEEKBEG WEEKEND DBNAME NAME AUTHID USES,PLANNAME;
  DEFINE WEEKBEG/ 'Week Beg' format=E8601DA10.;            
  DEFINE WEEKEND/ 'Week End' format=E8601DA10.;            
  DEFINE DBNAME/ 'Database' format=$8. group;              
  DEFINE NAME/ 'Table' format=$32. group;                  
  DEFINE AUTHID/ 'ID' format=$10. group;                   
  DEFINE PLANNAME/ACROSS ' ' format=$11.;                  
  DEFINE USES/ANALYSIS ' ' SUM FORMAT=9.;                  
  COMPUTE BEFORE;                                          
     DBNAME='*TOTAL*';                                     
  ENDCOMP;                                                 
  RBREAK BEFORE / summarize;                               
RUN;                                                       
ODS TAGSETS.CSVALL CLOSE;
RUN;  

[Kurt_Bremser]

Why so complicated? Since a csv needs no prettying up at all, a simple data step will suffice.

data _null_;
file "&pathname./&filename..csv" dlm=',';
set sum2;
format
  WEEKBEG E8601DA10.
  WEEKEND E8601DA10.
  DBNAME $8.
  NAME $32.
  AUTHID $10.
  PLANNAME $11.
  USES 9.
;
if _n_ = 1 then put "WEEKBEG,WEEKEND,DBNAME,NAME,AUTHID,PLANNAME,USES";
put
  WEEKBEG
  WEEKEND
  DBNAME
  NAME
  AUTHID
  PLANNAME
  USES
;
run;

For summary lines, add the necessary by-group processing.

[TimH]

A simple summary will not work because the "USE,PLANNAME"  in the COLUMN definition in conjunction with the definition of PLANNAME as an ACROSS variable creates a column for each plan and this is what the requesting user desires.  I proposed your solution but that is not what they want.

[Reeza]

---

@TimH wrote:

A simple summary will not work because the "USE,PLANNAME"  in the COLUMN definition in conjunction with the definition of PLANNAME as an ACROSS variable creates a column for each plan and this is what the requesting user desires.  I proposed your solution but that is not what they want.

---

Not sure how an ACROSS variable is an issue, you can create an output format that matches in a data set using PROC MEANS and TRANSPOSE or PROC TABULATE and an OUT option. Or in a data step. Or in SQL. There are a lot of ways. Perhaps it would be easier if you posted your data and what you wanted as final output.  You may be adding some steps but it's faster and easier overall in the end and the user gets what they want. 

Here's an example that shows how you can replicate data on each row using PROC REPORT:

https://communities.sas.com/t5/ODS-and-Base-Reporting/proc-report-repeat-the-group-value-for-each-li...

[TimH]

Thanks - the 'revised' example in that link will probably work for me.  If it doesn't I will try TABULATE I guess.

[Reeza]

You wouldn't use PROC REPORT to create a CSV. It adds too much formatting. Try PROC PRINT, a data step or proc export instead.

In your particular case run PROC MEANS first to get the summaries or PROC PRINT will add a total row withe the SUM statement.

---

@TimH wrote:

I need to write a CSV file from PROC REPORT output, but this CSV is intended to be loaded into a relational table.  For that reason, I need each of the variables to be populated in every row of the CSV, whether they are grouping variables or not.  The code is below.  I have the OUT= because I was going to investigate the output to see if it was better to wrap the ODS code around a PROC PRINT of that dataset but I thought I'd ask here first.  This code works fine, but the grouping variables in the CSV are in 'outline' form (the value is only populated when it changes) and that won't work for loading into a table. Is there something I can add to make them displayed on every row?

ODS TAGSETS.CSVALL                                         
           PATH="pathname"                               
           file="filenname.csv";                                          
TITLE;                                                     
PROC REPORT DATA=SUM2 OUT=MYRPT;                           
  BY WEEKBEG WEEKEND;                                      
  COLUMN WEEKBEG WEEKEND DBNAME NAME AUTHID USES,PLANNAME;
  DEFINE WEEKBEG/ 'Week Beg' format=E8601DA10.;            
  DEFINE WEEKEND/ 'Week End' format=E8601DA10.;            
  DEFINE DBNAME/ 'Database' format=$8. group;              
  DEFINE NAME/ 'Table' format=$32. group;                  
  DEFINE AUTHID/ 'ID' format=$10. group;                   
  DEFINE PLANNAME/ACROSS ' ' format=$11.;                  
  DEFINE USES/ANALYSIS ' ' SUM FORMAT=9.;                  
  COMPUTE BEFORE;                                          
     DBNAME='*TOTAL*';                                     
  ENDCOMP;                                                 
  RBREAK BEFORE / summarize;                               
RUN;                                                       
ODS TAGSETS.CSVALL CLOSE;
RUN;  

---

[TimH]

Please note that I need the PLANNAME as an ACROSS variable.

[TimH]

Let me clarify - if I were to go to PROC MEANS or PROC TABULATE for this,  I would need to create a variable for each of the plan names with a value of 1 or 0, and summarize all of those variables.  That might work if we limit our selection to a finite group of plan names, and in this particular instance we might be able to do that.  

It would be better, though, to have the option to just have those values filled in on every line, even if they _are_ grouping variables.

[ballardw]

The output data set probably has the group variables correctly. The issue is that the ACROSS variable  may no longer be there.

As I mentioned, you have not provided any example of what the actual output should look like.

You may be able to get something like what you want with Proc Summary (or means) and Transpose IF the value of planname can be a valid SAS variable name.

And example using data you should have available:

proc summary data= sashelp.class nway;
   class age sex name;
   var weight;
   output out=work.sum sum=;
run;

proc transpose data=work.sum out=work.trans;
   by age sex;
   id name;
   var weight;
run;

The _name_ variable could be dropped from work.trans.

The CLASS variables in proc summary would be all of the group variables and the last element would be the Across variable.

This won't quite work if there are more than one variable to summarize as the "across" variable can only be used for one column name out of proc transpose.

[Reeza]

The variable planname can also use the IDlabel and PROC EXPORT will print the label not the variable name. 

---

@ballardw wrote:

The output data set probably has the group variables correctly. The issue is that the ACROSS variable  may no longer be there.

As I mentioned, you have not provided any example of what the actual output should look like.

You may be able to get something like what you want with Proc Summary (or means) and Transpose IF the value of planname can be a valid SAS variable name.

And example using data you should have available:

proc summary data= sashelp.class nway;
   class age sex name;
   var weight;
   output out=work.sum sum=;
run;

proc transpose data=work.sum out=work.trans;
   by age sex;
   id name;
   var weight;
run;

The _name_ variable could be dropped from work.trans.

The CLASS variables in proc summary would be all of the group variables and the last element would be the Across variable.

This won't quite work if there are more than one variable to summarize as the "across" variable can only be used for one column name out of proc transpose.

---

[ballardw]

If you can't share starting data and exactly what the result should be for that example data this is going to be frustrating for everyone concerned.

[Tom]

Just convert the data 

proc transpose data=sum2 out=myrpt ;
  by weekbeg weekend dbname name authid ;
  id planname ;
  var uses ;
run;

and then generate a CSV file.

data _null_;
  file    "pathname/filenname.csv" dsd ;
  set myrpt;
  put (_all_) (+0);
run;

If you need to add header row you can do that also. https://github.com/sasutils/macros/blob/master/csv_vnext.sas