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.
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
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;
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
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
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
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
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
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.
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.
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.