topic Re: Proc Report Formatting Question in ODS and Base Reporting

Proc Report Formatting Question

sasxtra1 — Sat, 07 Jun 2014 04:03:25 GMT

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.

Re: Proc Report Formatting Question

Cynthia_sas — Sat, 07 Jun 2014 04:34:56 GMT

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;

Re: Proc Report Formatting Question

sasxtra1 — Sat, 07 Jun 2014 11:52:27 GMT

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

Re: Proc Report Formatting Question

Cynthia_sas — Sat, 07 Jun 2014 16:44:18 GMT

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

Re: Proc Report Formatting Question

sasxtra1 — Sun, 08 Jun 2014 03:38:47 GMT

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

Re: Proc Report Formatting Question

Ksharp — Sun, 08 Jun 2014 04:33:49 GMT

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

Re: Proc Report Formatting Question

Cynthia_sas — Sun, 08 Jun 2014 05:37:56 GMT

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

Re: Proc Report Formatting Question

sasxtra1 — Thu, 12 Jun 2014 17:38:31 GMT

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.