Creating a Table with Horizontal Date

[BCNAV]

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

[jklaverstijn]

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

[ballardw · Accepted solution]

---

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

[BCNAV]

@ballardw

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!!

[BCNAV]

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

[ballardw]

---

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