topic Re: PROC REPORT to CSV with values in all columns in each row in ODS and Base Reporting

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

TimH — Wed, 05 Sep 2018 14:05:08 GMT

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;

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

Kurt_Bremser — Wed, 05 Sep 2018 14:19:21 GMT

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.

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

TimH — Wed, 05 Sep 2018 14:57:54 GMT

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.

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

Reeza — Wed, 05 Sep 2018 14:58:19 GMT

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;

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

TimH — Wed, 05 Sep 2018 15:13:31 GMT

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

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

TimH — Wed, 05 Sep 2018 15:20:15 GMT

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.

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

ballardw — Wed, 05 Sep 2018 15:22:29 GMT

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.

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

Reeza — Wed, 05 Sep 2018 15:25:41 GMT

@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-lines/td-p/409184

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

TimH — Wed, 05 Sep 2018 15:58:36 GMT

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

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

Tom — Wed, 05 Sep 2018 16:53:13 GMT

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

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

ballardw — Wed, 05 Sep 2018 16:54:07 GMT

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.

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

Reeza — Wed, 05 Sep 2018 16:56:59 GMT

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.