BookmarkSubscribeRSS Feed
wildcats
Calcite | Level 5

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? 

 

Dataimage.jpg

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. 

5 REPLIES 5
ballardw
Super User

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
Super User Tom
Super User

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
Super User

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;


 

Reeza_0-1695315885099.png

 

ballardw
Super User

@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? 

 

Dataimage.jpg

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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 777 views
  • 0 likes
  • 5 in conversation