topic Transpose and label data in SAS Programming

Transpose and label data

Q1983 — Mon, 25 Feb 2019 01:50:25 GMT

data have;

length Group $50;

input Group $ _NAME_ $ Value ;

datalines;

WWWW AARP 1

TTTT BBRP 3

DDDD BBRP 2

EEEE BBRP 4

run;

I want to be able to tanspose the data and assign a label based on Value field as follows;

if the Value 1 '1_1_29' If the Value is 2 then '2_30_59', if Value is 3 then '3_60_89' if 4 then '4_90_119' so I want to transpose and assign a label value based on the Value number.

Re: Transpose and label data

ChrisNZ — Mon, 25 Feb 2019 02:10:20 GMT

Show us the desired output please.

Re: Transpose and label data

PGStats — Mon, 25 Feb 2019 04:45:39 GMT

You could mean something like this:

proc format;
value myFormat
1 = '1_1_29'
2 = '2_30_59'
3 = '3_60_89'
4 = '4_90_119';
run;

data have;
length Group $50;
input Group $ _NAME_ $ Value;
format value myFormat.;
datalines;
WWWW AARP 1
TTTT BBRP 3
DDDD BBRP 2
EEEE BBRP 4
; 

proc transpose data=have out=want(drop=junk) name=junk;
by group notsorted;
var value;
id _name_;
run;

proc print data=want noobs; run;

                         Group      AARP        BBRP

                         WWWW     1_1_29             .
                         TTTT            .    3_60_89
                         DDDD            .    2_30_59
                         EEEE            .    4_90_119

Just a guess.

Re: Transpose and label data

Q1983 — Mon, 25 Feb 2019 16:06:59 GMT

data have;

length Group $50;

input Group $ _NAME_ $ Value ;

datalines;

WWWW AARP 1

TTTT BBRP 3

DDDD BBRP 2

EEEE BBRP 4

run;

I want to be able to tanspose the data and assign a label based on Value field as follows;

if the Value 1 '1_1_29' If the Value is 2 then '2_30_59', if Value is 3 then '3_60_89' if 4 then '4_90_119' so I want to transpose and assign a label value based on the Value number.

So this is the desired result.

Group Name _1_1_29 _2_30_59 _3_60_89 _4_90_119 GrTotal

WWWW AARP 1 1

TTTT BBRP 3 3

DDDD BBRP 2 2

EEEE BBRP 4 4

So essentially I am transposing the VALUE. Another responder mentioned proc format

Re: Transpose and label data

Tom — Mon, 25 Feb 2019 19:40:06 GMT

Seems like a useless format and very,very strange variable names.

But if your values are nice simple integers then just use them as indexes into an array.

data want;
  set have ;
  array new _1_1_29     _2_30_59    _3_60_89    _4_90_119 ;
  new[Value] = Value ;
run;

Perhaps your real data has multiple observations per GROUP NAME?

data want;
do until(last._name_);
  set have ;
  by group _name_;
  array new _1_1_29     _2_30_59    _3_60_89    _4_90_119 ;
  new[Value] = Value ;
end;
  gr_total = sum(of new[*]);
run;