topic Re: Add wanted Label value column in SAS Programming

Add wanted Label value column

Ronein — Sat, 25 Jul 2020 17:47:53 GMT

Hello

I have a wide structure summary table.

In this table for each Business ID there are 9 columns with 3 groups of variables:

a-amount of Income in each period (YYMM).Fields: amount2001 amount2004 amount2005

b-Number of bank transactions in each period (YYMM).Fields: NrTrans2001,NrTrans2004,NrTrans2005

c-Number of customers in each period (YYMM) .Fields: NrCustomers2001,NrCustomers2004,NrCustomers2005

I want to add a new column to this wide summary table with name of wanted label.(name of field :Wanted_label

The new column values will be as following:

For amount fields the label will be : Income YYMM

For NrTrans fields the label will be : Number Transactions YYMM

For NrCustomers fields the label will be : Number Customers YYMM

What is the clever way to do it please ?

In real world I have many fields and I think that it is not clever way to type for each of them the label value.

Data LongSummaryTbl;
Input BusinessID Field $ value;
cards;
1 amount2001 10
1 amount2004 12
1 amount2005 14
1 NrTrans2001 16
1 NrTrans2004 18
1 NrTrans2005 13
1 NrCustomers2001 8
1 NrCustomers2004 4
1 NrCustomers2005 7
;
Run;


Data LongSummaryTbl2;
set LongSummaryTbl;

IF field='amount2001' then Wanted_label='Income 2001';
IF field='amount2004' then Wanted_label='Income 2004';
IF field='amount2005' then Wanted_label='Income 2005';

IF field='NrTrans2001' then Wanted_label=' Number Transactions 2001';
IF field='NrTrans2004' then Wanted_label=' Number Transactions 2004';
IF field='NrTrans2005' then Wanted_label=' Number Transactions 2005';

IF field='NrCustomers2001' then Wanted_label=' Number Customers 2001';
IF field='NrCustomers2004' then Wanted_label=' Number Customers 2004';
IF field='NrCustomers2005' then Wanted_label=' Number Customers 2005';

Run;

Re: Add wanted Label value column

ChrisNZ — Sat, 25 Jul 2020 09:16:24 GMT

Please format your code.

Re: Add wanted Label value column

Ronein — Sat, 25 Jul 2020 09:33:14 GMT

What do you mean please?

Re: Add wanted Label value column

Astounding — Sat, 25 Jul 2020 10:10:44 GMT

The clever thing to do has been posted on this board many times, by a number of people. I'm not sure if it has ever been posted in answer to one of your questions, so now is the time.

The clever thing to do is to store your data in long format instead of wide format. You have come up with one more reason why this is wise.

Re: Add wanted Label value column

Ronein — Sat, 25 Jul 2020 10:18:55 GMT

Thank you for your response.

As you can see the data structure is long and not wide!

My questions was how to create a new column that will contain wanted labels names.

As you can see in my post I created the new column with manual typing the label and my question was how to do it in a better way.

Re: Add wanted Label value column

PaigeMiller — Sat, 25 Jul 2020 10:21:46 GMT

@Ronein wrote:

What do you mean please?

Your code is very difficult to read, and needs to be formatted properly so we can read it.

Re: Add wanted Label value column

Ronein — Sat, 25 Jul 2020 10:28:00 GMT

I think that this code will solve the problem but I haven't checked it because I don't have SAS in my personal PC.

What do you think?Will it work well?

Data Long2;
Set Long;
YYMM=SUBSTR(field,LENGTH(TRIM(Field))-4);							
If Substr(Field,1,6)="amount" then wantedlabel=catx(' ','Income',yymm);							
If Substr(Field,1,7)="NrTrans" then wantedlabel=catx(' ','Number Transactions ',yymm);							
If Substr(Field,1,11)="NrCustomers " then wantedlabel=catx(' ','Number Customers ',yymm);	
Run;

Re: Add wanted Label value column

PaigeMiller — Sat, 25 Jul 2020 10:43:47 GMT

Data Long2;Set Long;YYMM=SUBSTR(field,LENGTH(TRIM(Field))-4);

I find three commands on one line to be difficult to read, and contrary to what almost everyone else does.

In any case, what are you trying to do here? If you need YYMM in a separate column, that makes sense to me and is easy to do, but why would you need to change "amount" to "income", etc. and then append YYMM?

yymm=scan(field,-1,'_');

Re: Add wanted Label value column

ChrisNZ — Sat, 25 Jul 2020 12:48:06 GMT

> What do you mean please?

Just look You second data step is all on one line.

Re: Add wanted Label value column

Tom — Sat, 25 Jul 2020 17:49:03 GMT

@Ronein wrote:

What do you mean please?

If you edit the code in a code box without re-opening the code box then the line breaks can get messed up. Normally just opening the Insert SAS Code box and closing it will fix the issue.

Re: Add wanted Label value column

Tom — Sat, 25 Jul 2020 17:53:40 GMT

Looks like you just want to use TRANWRD() function.

data LongSummaryTbl2;
  set LongSummaryTbl;
  length Wanted_Label $50 ;
  wanted_label = field;
  wanted_label = tranwrd(wanted_label ,'amount','Income ');
  wanted_label = tranwrd(wanted_label ,'NrTrans',' Number Transactions ');
  wanted_label = tranwrd(wanted_label ,'NrCustomers',' Number Customers ');
run;

Re: Add wanted Label value column

Ronein — Sat, 25 Jul 2020 19:13:33 GMT

This is a problem with this web site!!!

I write it in multiple rows and It appears in one line!

Re: Add wanted Label value column

ChrisNZ — Sun, 26 Jul 2020 05:59:11 GMT

Yes this website can corrupt formatting when you alter lines after pasting.

It is up to you to ensure that your post is legible.

Delete and paste again.