I am trying to eliminate an Excel step and create a table for export in SAS. We report on certain airline groups on a rolling 15 month basis. the data is created in SAS fine and is structured as (CG= contribution to growth):
client_id Year Month CG
N11111 2017 2 5%
N11112 2017 2 6%
N11111 2017 3 7%
N11112 2017 3 4%
etc.
Note that the number of client_ids stay the same each month (the are static definitions).
The table output that would be nice is in the form (for 15 months horizontally):
year year year year year year
client_id month month month month month month
N11111 0.5% 0.5% 0.5% 0.5% 0.5% 0.5%
N11112 0.6% 0.6% 0.6% 0.6% 0.6% 0.6%
etc.
The latest 15 month period would have the year ranging from 2017 to 2018 with the months Feb 2017 to April 2018. Again the data is fine and in the format listed at the start of the post.
I am attaching a pdf of the output format that is needed. Can SAS handle this?
Thanks
-Bill
@BCNAV wrote:
I am trying to eliminate an Excel step and create a table for export in SAS. We report on certain airline groups on a rolling 15 month basis. the data is created in SAS fine and is structured as (CG= contribution to growth):
client_id Year Month CG
N11111 2017 2 5%
N11112 2017 2 6%
N11111 2017 3 7%
N11112 2017 3 4%
etc.
Note that the number of client_ids stay the same each month (the are static definitions).
The table output that would be nice is in the form (for 15 months horizontally):
year year year year year year
client_id month month month month month month
N11111 0.5% 0.5% 0.5% 0.5% 0.5% 0.5%
N11112 0.6% 0.6% 0.6% 0.6% 0.6% 0.6%
etc.
The latest 15 month period would have the year ranging from 2017 to 2018 with the months Feb 2017 to April 2018. Again the data is fine and in the format listed at the start of the post.
I am attaching a pdf of the output format that is needed. Can SAS handle this?
Thanks
-Bill
Since you have a requirement to select an specific interval of date values then the first step really should be to ensure that you have SAS date values. You do not indicate which are particularly critical appearance items. For instance you show Year and month in two separate cells of the spreadsheet with Year apparently numeric and month as the full name of the month. Is it critical that each appear in a single separate cell or would a single cell that read 2017 February be acceptable?
Custom formats can create many different appearances or groups from data just by changing a format and very helpful with dates.
One brief example:
data have; input client_id $ Year Month CG : ; date = mdy(month,1,year); format date yymon8.; datalines; N11111 2017 2 .05 N11112 2017 2 .06 N11111 2017 3 .07 N11112 2017 3 .04 ; proc report data=have nowd; columns client_id date,cg; define client_id /group; define date / across; run;
Or a different format for the date:
proc format library=work; picture yearmonth (default=14) low - high = '%Y %9B' (datatype=date) ; run; proc report data=have nowd; columns client_id date,cg; define client_id /group; define date / across order=internal; format date yearmonth.; run;
Summaries and appearance of variable labels are details. Proc Tabulate will also create headers across using formatted values.
Of course SAS can handle this. If you wouldn't have the Excel requirement than proc tabulate would have taken care of the report nicely and in a mere minute.
But since you need Excel the SAS result has to be data. You can use proc transpose for this. It was made for exactly this kind of problem. I will leave it to you (or someone else) to come up with the code. Or maybe you have Enterprise Guide at your disposal which can do the programming for you.
An approach using datasetep is also possible. Personally I would go for the more automatic way and not program more than needed.
Hope this helps,
-Jan
@BCNAV wrote:
I am trying to eliminate an Excel step and create a table for export in SAS. We report on certain airline groups on a rolling 15 month basis. the data is created in SAS fine and is structured as (CG= contribution to growth):
client_id Year Month CG
N11111 2017 2 5%
N11112 2017 2 6%
N11111 2017 3 7%
N11112 2017 3 4%
etc.
Note that the number of client_ids stay the same each month (the are static definitions).
The table output that would be nice is in the form (for 15 months horizontally):
year year year year year year
client_id month month month month month month
N11111 0.5% 0.5% 0.5% 0.5% 0.5% 0.5%
N11112 0.6% 0.6% 0.6% 0.6% 0.6% 0.6%
etc.
The latest 15 month period would have the year ranging from 2017 to 2018 with the months Feb 2017 to April 2018. Again the data is fine and in the format listed at the start of the post.
I am attaching a pdf of the output format that is needed. Can SAS handle this?
Thanks
-Bill
Since you have a requirement to select an specific interval of date values then the first step really should be to ensure that you have SAS date values. You do not indicate which are particularly critical appearance items. For instance you show Year and month in two separate cells of the spreadsheet with Year apparently numeric and month as the full name of the month. Is it critical that each appear in a single separate cell or would a single cell that read 2017 February be acceptable?
Custom formats can create many different appearances or groups from data just by changing a format and very helpful with dates.
One brief example:
data have; input client_id $ Year Month CG : ; date = mdy(month,1,year); format date yymon8.; datalines; N11111 2017 2 .05 N11112 2017 2 .06 N11111 2017 3 .07 N11112 2017 3 .04 ; proc report data=have nowd; columns client_id date,cg; define client_id /group; define date / across; run;
Or a different format for the date:
proc format library=work; picture yearmonth (default=14) low - high = '%Y %9B' (datatype=date) ; run; proc report data=have nowd; columns client_id date,cg; define client_id /group; define date / across order=internal; format date yearmonth.; run;
Summaries and appearance of variable labels are details. Proc Tabulate will also create headers across using formatted values.
Thanks...This nearly works (see attached PDF). But as you can see, the date columns are not in chronological order. Also, is it possible to get rid of the CG headings only? that way it is just client_id and date? I have created a date field as you suggested. I have also tried to get a date format that is like "Feb-2018", is this custom for SAS? Not sure what would go after MONYY to get the dash. Finally, is it possible to get a column total for each date?
Thanks..nearly there!!
So I have the following, which is nearly perfect:
/* options locale=en_US; */
ods listing close;
ods html close;
goptions device=actximg;
ods excel file="\\NCRFP4\TAF\Traffic Analysis\Routine\Top Airlines\Excel Working\Asian Group CG.xlsx";
ods excel options(start_at="1,1" sheet_name="CG" sheet_interval="table" embedded_titles='yes' absolute_column_width='10px');
proc report data=EGTASK.ASIAN_GRP_15MONTHS_2 nowd;
format date date9.;
title 'Asian Group Contribution to Growth (CG) of Total WCUs';
columns client_id date,cg;
define client_id / group left style(column)={tagattr='wraptext:no' width=100%};
define date / across order=internal style(column)={tagattr='wraptext:no' width=100%};
compute after;
client_id='Group Total';
endcomp;
rbreak after / summarize;
run;
ods excel close;
The only issues is that the exported excel does not recognize the exported date field as a date. It is formatted as general. How can I ensure that the date field is read as a date value?
Thanks
-Bill
@BCNAV wrote:
So I have the following, which is nearly perfect:
The only issues is that the exported excel does not recognize the exported date field as a date. It is formatted as general. How can I ensure that the date field is read as a date value?
Thanks
-Bill
Use TAGATTR to include the Excel format should work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: