The SAS Output Delivery System and reporting techniques

Proc Report Formatting Question

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Proc Report Formatting Question

I have input SAS dataset that has repeating variables that I would like to print on multiple lines.

Input: a1,a2,a3-1,a3-2, a3-3

I would like to print this as

a1 a2 a3-1

         a3-2

         a3-3

If a1 = Name, a2 = Lesson, a3-1 = date1, a3-2 = date2, a3-3 = date3

I would like get the output to look like this.

Name     Lesson   Date

Tom         SAS    02/05/2014

                           03/14/2014

                           04/02/2014

 
I have searched the Internet and cannot find an answer. Any help would be appreciated.


Accepted Solutions
Solution
‎06-07-2014 12:44 PM
SAS Super FREQ
Posts: 8,819

Re: Proc Report Formatting Question

Hi:

  You may need to restructure your data. PROC REPORT does not normally "stack" variables like your date and percent into one cell. It would be easier to make PROC REPORt work (you could use my code) if your data was in this structure:

 

datalines;

"INDIANA","RED",01/01/2013,10

"INDIANA","RED",02/01/2013,10

"INDIANA","RED",03/01/2013,10

"OHIO","BLUE",01/01/2013,15

"OHIO","BLUE",02/01/2013,15

"OHIO","BLUE",03/01/2013,13

     

...which you could do with a DATA step or PROC TRANSPOSE.

    

cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,819

Re: Proc Report Formatting Question

Hi,

  You do not show enough data to get an idea of the structure of each observation. What code have you tried? What is your destination of interest?

      

  Take a look at the stucture of the data below and the output that it produces. It looks similar to what you describe, but the structure of the "fake" data might not be like your data.

cynthia

data work.example;
  infile datalines dlm=',' dsd;
  input country $ division $ date : mmddyy10. amount;
return;
datalines;
"CANADA","CONSUMER",01/01/2013,4732.00
"CANADA","CONSUMER",02/01/2013,3814.00
"CANADA","CONSUMER",03/01/2013,4692.00
"CANADA","SPECIAL",01/01/2013,3383.00
"CANADA","SPECIAL",02/01/2013,6617.00
"CANADA","SPECIAL",03/01/2013,5766.00
"U.S.A.","CONSUMER",01/01/2013,6270.00
"U.S.A.","CONSUMER",02/01/2013,4488.00
"U.S.A.","CONSUMER",03/01/2013,4262.00
"U.S.A.","SPECIAL",01/01/2013,4189.00
"U.S.A.","SPECIAL",02/01/2013,5368.00
"U.S.A.","SPECIAL",03/01/2013,5381.00
;
run;
  
ods html file='c:\temp\report.html' ;
proc report data=work.example nowd spanrows;
  column country division date amount;
  define country /order;
  define division / order;
  define date / f=mmddyy10. order order=internal;
  define amount / sum;
  break after country / summarize;
run;
ods html close;

Contributor
Posts: 23

Re: Proc Report Formatting Question

The observatons are in the order that I want already. There are repeating groups in each observation that I am trying to print on seperate lines for each observation. There is also non-repeating information for each observation. I hope this helps as I am stuck trying to figure it out.

datalines;

"INDIANA","RED",01/01/2013,02/01/2013,03/01/2013,10,10,10

"OHIO","BLUE",01/01/2013,02/01/2013,03/01/2013,15,15,13

"MICHIGAN","GREEN",01/01/2013,02/01/2013,03/01/2013,12,12,11

"NEVADA","GREEN",01/01/2013,02/01/2013,03/01/2013,15,15,10

"CALIFORNIA","RED",01/01/2013,02/01/2013,03/01/2013,15,15,13

"ALASKA","RED",01/01/2013,02/01/2013,03/01/2013,15,15,13

;

State    Status     Date       Percentage

INDIANA   RED     01/01/2013       10

                  02/01/2013       10

                  03/01/2013       10

OHIO     BLUE     01/01/2013       15

                  02/01/2013       15

                  03/01/2013       13

MICHIGAN GREEN    01/01/2013       12

                  02/01/2013       12

                  03/01/2013       11

NEVADA   GREEN    01/01/2013       15

                  02/01/2013       15

                  03/01/2013       10

Solution
‎06-07-2014 12:44 PM
SAS Super FREQ
Posts: 8,819

Re: Proc Report Formatting Question

Hi:

  You may need to restructure your data. PROC REPORT does not normally "stack" variables like your date and percent into one cell. It would be easier to make PROC REPORt work (you could use my code) if your data was in this structure:

 

datalines;

"INDIANA","RED",01/01/2013,10

"INDIANA","RED",02/01/2013,10

"INDIANA","RED",03/01/2013,10

"OHIO","BLUE",01/01/2013,15

"OHIO","BLUE",02/01/2013,15

"OHIO","BLUE",03/01/2013,13

     

...which you could do with a DATA step or PROC TRANSPOSE.

    

cynthia

Contributor
Posts: 23

Re: Proc Report Formatting Question

I am able to intercept the data before it gets summarized, so I think the problem is easiser, but still has issues.

I need to have the States with the highest percentage in the latest date first, and have the three observations for each State listed in date sequence high to low.

So even though Michigan and Nevada below have high percentages, they where not in the last observation.

I hope I explained everything correct, since you've been a great help so far.

datalines;

"INDIANA","RED",01/01/2013,10

"INDIANA","RED",02/01/2013,10

"INDIANA","RED",03/01/2013,10

"OHIO","BLUE",01/01/2013,15

"OHIO","BLUE",02/01/2013,16

"OHIO","BLUE",03/01/2013,17

"MICHIGAN","GREEN",01/01/2013,18

"MICHIGAN","GREEN",02/01/2013,12

"MICHIGAN","GREEN",03/01/2013,13

"NEVADA","GREEN",01/01/2013,18

"NEVADA","GREEN",02/01/2013,10

"NEVADA","GREEN",03/01/2013,12

"CALIFORNIA","RED",01/01/2013,11

"CALIFORNIA","RED",02/01/2013,13

"CALIFORNIA","RED",03/01/2013,15

;

State    Status     Date       Percentage

OHIO     BLUE     03/01/2013       17

                  02/01/2013       16

                  01/01/2013       15

CALIFORNIA RED   03/01/2013       15

                  02/01/2013       13

                  01/01/2013       11

MICHIGAN GREEN    03/01/2013       13

                  02/01/2013       12

                  01/01/2013       18

NEVADA   GREEN    03/01/2013       12

                  02/01/2013       10

                  01/01/2013       18

INDIANA   RED     03/01/2013       10

                  02/01/2013       10

                  01/01/2013       10

Super User
Posts: 9,867

Re: Proc Report Formatting Question

Modify Cynthia's code a little bit :

data work.example;
  infile datalines dlm=',' dsd;
  input country $ division $ date : mmddyy10. amount;
return;
datalines;
"INDIANA","RED",01/01/2013,10
"INDIANA","RED",02/01/2013,10
"INDIANA","RED",03/01/2013,10
"OHIO","BLUE",01/01/2013,15
"OHIO","BLUE",02/01/2013,16
"OHIO","BLUE",03/01/2013,17
"MICHIGAN","GREEN",01/01/2013,18
"MICHIGAN","GREEN",02/01/2013,12
"MICHIGAN","GREEN",03/01/2013,13
"NEVADA","GREEN",01/01/2013,18
"NEVADA","GREEN",02/01/2013,10
"NEVADA","GREEN",03/01/2013,12
"CALIFORNIA","RED",01/01/2013,11
"CALIFORNIA","RED",02/01/2013,13
"CALIFORNIA","RED",03/01/2013,15
;
run;
   
ods html file='c:\temp\report.html' ;
proc report data=work.example nowd spanrows;
  column country division date amount;
  define country /order;
  define division / order;
  define date / f=mmddyy10. order order=internal descending;
  define amount / sum;
  break after country / summarize;
run;
ods html close;

Xia Keshan

SAS Super FREQ
Posts: 8,819

Re: Proc Report Formatting Question

Hi:

  I don't understand what determines the order for the rows. Is it the total highest percentage for each state? If so, then I get Ohio (48), Michigan (43), Nevada (40), California (39), Indiana (30)?  It looks like no matter what, you always have date in descending order, even if the highest monthly percent is a different month than 03/01 (see Nevada where 03/01 is 12 and 01/01 is 18). Or, is it only the highest percent in the most recent month (March percents, in this case)?

  Look at the "ordering" example #3 http://www2.sas.com/proceedings/forum2008/173-2008.pdf starting on page 11. You may need to make an "ordering" variable so that the rows can be ordered by the ordering variable.

cynthia

Contributor
Posts: 23

Re: Proc Report Formatting Question

Thanks for your help, I think all the issues are solved.

I needed to sort groups of data which I did by adding a new variable with a code to hold the rank of each group. I was able to produce the report finally.

I am trying to absorb as much information about SAS as I can but would like to see an 'Examples' database to search on. I leaned heavily on the Internet but that is hit and miss. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 801 views
  • 0 likes
  • 3 in conversation