combining rows by date

[wildcats]

I have transformed this table with imported data, but need to merge the collection of rows into individual rows by date. This would eliminate the excess of empty cells. Right now there are hundreds of rows when. I want them to condensed in to the three separate dates that data was collected. I have no experience with SAS, I appreciate the more specific and detailed you are. How do I begin? What procedure do I use? 

 

I know the photo is not great, but you can see that it created the same date entry for each column, when it could be condensed into one.  I used if statements and drop statements to filter the data I wanted to utilize.  Other than that, the following is the is the only code in the program. 

proc sort;
by REPORT_DATE;

proc report data=one out=sorted ;
column REPORT_DATE LEVEL_2, (STORES_WITH_ADS WEIGHTED_AVERAGE_PRICE);
define REPORT_DATE / display;
define LEVEL_2/ across;

proc print;
run;

When I tried to

define REPORT_DATE / group; 

it removes my column titles and transforms my graph back to a long dataset rather than a wide and still prints the same date multiple times. 

[ballardw]

How about showing how you created that output. Likely it can be done before creating that mostly empty "table".

Best is to provide some example data in the form of a working data step and the code used to make the "table".

[Tom]

Not sure what you posted in the PDF file as they are blocked by my company's intranet settings. 

If you want to post text just use the insert code button to get a pop-up to appear where you can paste/type the text.  If you want to post a picture either use the Insert Photos button to upload a picture file, or just paste the image directly into your post.

If you want REPORT_DATE to GROUP the data then tell PROC REPORT that in the DEFINE statement.

If you want some other aggregate function than SUM for the two analysis variables then you will need to add DEFINE statements for them also.

[Reeza]

Just a note, we can see what you have, but what do you want? How do you want to reduce the empty columns, how do you want to combine them?

Please show a small reproducible example.

---

@wildcats wrote:

I have transformed this table with imported data, but need to merge the collection of rows into individual rows by date. This would eliminate the excess of empty cells. How do I begin? What procedure do I use? 

I used if statements and drop statements to filter the data I wanted to utilize.  Other than that, the following is the is the only code in the program. 

proc sort;
by REPORT_DATE;

proc report data=one out=sorted ;
column REPORT_DATE LEVEL_2, (STORES_WITH_ADS WEIGHTED_AVERAGE_PRICE);
define REPORT_DATE / display;
define LEVEL_2/ across;

proc print;
run;

---

[ballardw]

---

@wildcats wrote:

I have transformed this table with imported data, but need to merge the collection of rows into individual rows by date. This would eliminate the excess of empty cells. Right now there are hundreds of rows when. I want them to condensed in to the three separate dates that data was collected. I have no experience with SAS, I appreciate the more specific and detailed you are. How do I begin? What procedure do I use? 

 

I know the photo is not great, but you can see that it created the same date entry for each column, when it could be condensed into one.  I used if statements and drop statements to filter the data I wanted to utilize.  Other than that, the following is the is the only code in the program. 

proc sort;
by REPORT_DATE;

proc report data=one out=sorted ;
column REPORT_DATE LEVEL_2, (STORES_WITH_ADS WEIGHTED_AVERAGE_PRICE);
define REPORT_DATE / display;
define LEVEL_2/ across;

proc print;
run;

When I tried to

define REPORT_DATE / group; 

it removes my column titles and transforms my graph back to a long dataset rather than a wide and still prints the same date multiple times. 

---

What graph? Where? I don't see any code to create a graph.

If you mean in the result set Sorted on your proc report perhaps you are going about making the graph incorrectly.

I personally would never bother to make an output set with an across variable as shown if I intend to graph it.

If the purpose of that proc report was to create sums of two variables and intended to graph the result I would look at something more like:

Proc summary data=one nway;
   class report_date level_2;
   var stores_with_ads weighted_average_price;
  output out=summary (drop = _type_ _freq_) sum= ;
run;

If you do this and still get more than one row showing the same "date" with the same value of level_2 then you have a problem with the values of your report_date variable. What FORMAT is currently assigned to the REPORT_DATE variable?

[mkeintz]

If the data are sorted by report_date, then you can make a dataset with one obs per report date, with all the non-missing values for other variables, in four statements:

data want;
  update have (obs=0) have;
  by report_date;
run;

This assumes that no column has more than one non-missing value for each report date.