topic Re: Create Dynamic labels process in SAS Programming

Create Dynamic labels process

Ronein — Sun, 19 Jul 2020 16:01:30 GMT

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;

Re: Create Dynamic labels process

PaigeMiller — Sun, 19 Jul 2020 17:14:14 GMT

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.

Re: Create Dynamic labels process

Ronein — Sun, 19 Jul 2020 17:40:04 GMT

I am sorry but I don't understand your answer.
The required report included 100 columns for each school.
The information included statistics for different point of time.
This is the business requirement.
Since ,there are groups of columns with same meaning but different time point then I have asked how can I define label for each of them without modifying it manually in each run.
For example:
In the report that I run today there are 4 columns with number of customers :
Nr_Customers2005
Nr_Customers2004
Nr_Customers2001
Nr_Customers1912

But in the report that I run next week there will be 5 columns:
Nr_Customers2006
Nr_Customers2005
Nr_Customers2004
Nr_Customers2001
Nr_Customers1912

Re: Create Dynamic labels process

PaigeMiller — Sun, 19 Jul 2020 17:46:38 GMT

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.

Re: Create Dynamic labels process

Ronein — Sun, 19 Jul 2020 17:56:58 GMT

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;

Re: Create Dynamic labels process

Ronein — Sun, 19 Jul 2020 18:51:34 GMT

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

Re: Create Dynamic labels process

PaigeMiller — Sun, 19 Jul 2020 18:52:58 GMT

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

Re: Create Dynamic labels process

Ronein — Sun, 19 Jul 2020 19:56:11 GMT

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!

Re: Create Dynamic labels process

PaigeMiller — Mon, 20 Jul 2020 10:33:05 GMT

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

Re: Create Dynamic labels process

Reeza — Mon, 20 Jul 2020 03:09:46 GMT

Then we get wide table?

How? Usually via PROC TRANSPOSE. When you do PROC TRANSPOSE there's the IDLABEL column that you can use to dynamically label your columns I will highly recommend this approach and NOT to rename them after the fact. Why? Because it's less error prone.
Otherwise you need to design your process for every product/group to ensure you have every value needed. What happens if you're missing a month, for a new product say? From years of doing this, renaming after the fact ASSUMES what the variable label is, but it's easy to find situations that break this, especially when doing it en masse. So use an approach that minimizes your work AND minimizes the likelihood of errors.

If you really, really want to rename after the fact the approach I would use would be to query SASHELP.VCOLUMN and create my labels/variable names and then use PROC DATASETS to rename/relabel it.

I show one approach on how to do that here: https://gist.github.com/statgeek/f18931085f6a0009185c

However, the IDLABEL statement is a better approach.