Proc Transpose with 2 ID columns and automatic label to new columns

[Ronein]

Hello

I have the following issue:

I need to transpose long data set into wide data set and during this process to create automatic labels to new columns.

It means that in the future if the long data set will contain more fields under "field column" then the labels will be created automatically.

For example:

I will have 4 fields: x2001,x2004,x2005,x2006 so Instead of typing label for each of them manually I want that the label will be created automatically for each of them : Reveue 2001, Revenue 2004, Revenue 2005,Revenue 2006.

Data Log;
Input CustID	Field $	YYMM $	Value;
cards;
  1	X	2001	10
1	X	2004	15
1	X	2005	20
1	X	2006	25
1	W	2001	11
1	W	2004	16
1	W	2005	22
1	W	2006	28
1	q	2001_2005	10
1	q	2001_2006	15
2	X	2001	12
2	X	2004	17
2	X	2005	22
2	X	2006	27
2	W	2001	13
2	W	2004	18
2	W	2005	24
2	W	2006	30
2	q	2001_2005	12
2	q	2001_2006	17
;
Run;

[ballardw]

While the ID statement in proc transpose will accept more than one variable and concatenate values to create variable names the IDLABEL statement requires a single variable. So create that variable in a data step before transpose.

Note: your data step does not create a long enough value for yymm to be used so I modified the data step to read it properly to get the entire value read.

Data Log;
Input CustID	Field $	YYMM :$10.	Value;
wantedlabel = catx(' ','Revenue',yymm);
cards;
1	X	2001	10
1	X	2004	15
1	X	2005	20
1	X	2006	25
1	W	2001	11
1	W	2004	16
1	W	2005	22
1	W	2006	28
1	q	2001_2005	10
1	q	2001_2006	15
2	X	2001	12
2	X	2004	17
2	X	2005	22
2	X	2006	27
2	W	2001	13
2	W	2004	18
2	W	2005	24
2	W	2006	30
2	q	2001_2005	12
2	q	2001_2006	17
;
Run;
proc transpose data=log let;
 by custid   ;
 id field yymm;
 idlabel wantedlabel;
 var value;
run; 

[Ronein]

Thank you,
The label for x fields should be :
wantedlabel = catx(' ','Revenue',yymm);

But the label for W fields should be :
 wantedlabel = catx(' ','Profit',yymm);

and also label for q2001_2005 should be : Delta Revenue 2001,2005

and also label for q2001_2006 should be : Delta Revenue 2001,2006

What is the way to do define these label please?

[Ronein]

What is the meaning of word "let" that you typed please in proc transpose?
Is it a key word?
What is it doing please?

[ballardw]

LET isn't needed and was left from an earlier try. It will process sets with duplicate values of the ID field, may be not as desired but will not stop with the duplicate ID errors, which was needed with your original data step code as the two-year hyphenated values were truncated at the last digit making the 2001_2005 and 2001_2006 both appear as 2001_200.

[ballardw]

---

@Ronein wrote:

Thank you,
The label for x fields should be :
wantedlabel = catx(' ','Revenue',yymm);

But the label for W fields should be :
 wantedlabel = catx(' ','Profit',yymm);

and also label for q2001_2005 should be : Delta Revenue 2001,2005

and also label for q2001_2006 should be : Delta Revenue 2001,2006

What is the way to do define these label please?

---

Probably easiest would be to create a custom format that maps the field value to the appropriate text and then use

Wantedlabel = catx(' ', put(field,fieldfmt.),yymm);

Since you did not provide that description in the text I didn't see it as I am not going to open XLSX files all the time.