BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasxtra1
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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;

sasxtra1
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

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

sasxtra1
Quartz | Level 8

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

Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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

sasxtra1
Quartz | Level 8

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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