Hello
I have the following problem:
I need to create a report every few days.
The report have dynamic number of fields.(Depends on business request)
I want to create a dynamic program that can give labels to columns without typing it manually each run.
For exaple:
For each column with name "Nr_Customers" I want to give label "Number Customer in YYMM".
So for column Nr_Customers2006 the label will be "Number Customer in 2006".
for column Nr_Customers2005 the label will be "Number Customer in 2005".
for column Nr_Customers2004 the label will be "Number Customer in 2004".
and so on
What is the way to tell SAS that for column "Nr_Customers" SAS will give label "Number Customer" plus "YYMM" that is 4 last digits from column name?
Data summary:
Input ID Month Nr_Customers2006 Nr_Customers2005 r_Customers2004 Nr_Customers1912 Nr_Customers1812 ;
cards;
1 10 20 30 40 50
2 20 30 40 50 60
3 15 20 25 30 35
4 5 10 15 20 25
5 30 25 20 35 38
;
Run;
Data wanted;
set summary;
label Nr_Customers2006='Number Customer in 2006'
Nr_Customers2005 ='Number Customer in 2005'
Nr_Customers2004='Number Customer in 2004'
Nr_Customers1912='Number Customer in 1912'
Nr_Customers1812='Number Customer in 1812';
Run;
The usual advice is to NOT put yearly (or monthly or calendar) information into separate variables, then you don't need to have calendar information in the variable names, and then you don't need calendar information in variable labels.
Furthermore, just because you want a report with these columns DOES NOT mean you need these columns in a data set. This makes your life much harder. With a long data set (rather than the wide one you are talking about) PROC REPORT easily makes the report without you ever having to make these columns in a data set with calendar information in their names/labels.
Example:
data have;
input id yymm number;
cards;
1 2005 28
1 2006 33
1 2007 49
2 2005 38
2 2006 12
2 2007 18
;
proc report data=have;
columns id yymm,number;
define id / group "ID";
define yymm / across "YYMM";
define number / analysis sum "Number";
run;
Wide data sets are not preferred. Long data sets are preferred. Wide data sets require much more coding. Long data sets require much less coding.
Maxims 11, 14 and 19.
If you run the code I provided, you will see what the relevance is to your problem, and you don't have to create the columns with calendar variable names or calendar information in the labels. It is completely dynamic, as the data changes, the columns in the report will change appropriately to match the data.
Thank you.
The report structure is wide.
May you show first how to change the structure to long and then to create the wide report with desired labels?
Nr='Number Customers "
Income='Total Income'
Data summaryTbl;
Input School $ Nr1912 Nr2001 Nr2004 Nr2005 Income1912 Income2001 Income2004 Income2005 ;
cards;
a 10 20 30 40 5 10 15 20
b 12 22 32 42 6 10 18 21
c 20 30 40 50 15 16 18 27
;
Run;
Moreover,
In the real report there are 10 "groups" of columns:
1-Nr_Customers
2-Income
3-Revenue
4-Wage_Cost
and so on
Should I perform 10 Proc reports (each one with across ) and then need to join the tables?
And then what is the benefit???
@Ronein wrote:
Thank you.
The report structure is wide.
May you show first how to change the structure to long and then to create the wide report with desired labels?
Nr='Number Customers "
Income='Total Income'
It doesn't matter that the report structure is wide. The data itself doesn't have to be wide, it is better off long. And so with a long data set, you can get a wide report.
So, how do you get the data? Can you explain that?
@Ronein wrote:
Many long reports and then I merge them to have one long report with many columns of statistical calculations. Most of statistics are calculated by proc sql with group by and then we get wide table!
So don't do that! Make the data set long, not wide. It doesn't even seem like you need the PROC SQL step. Just take the long data set and run PROC REPORT on it. Whatever statistics are computed by PROC SQL can be computed in PROC REPORT on a long data set producing a wide report.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.