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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

 

View solution in original post

5 REPLIES 5
jklaverstijn
Rhodochrosite | Level 12

Of course SAS can handle this. Smiley Happy 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
Super User

@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
Quartz | Level 8

@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
Quartz | Level 8

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
Super User

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

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
  • 5 replies
  • 1578 views
  • 0 likes
  • 3 in conversation