Help with reshape the data with proc transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Help with reshape the data with proc transpose

Hi There,

I have a data set below and would like to reshape it using proc transpose. Thanks, Bo

Have:

Intlabelantepostmiduplow
AwayL165.1366.6360.9376.0276.52
AwayL269.9765.1360.2079.3779.16
AwayL375.7768.1064.1778.8380.76
AwayL475.1366.3068.2781.1877.42
NormalL166.0067.5061.5081.0481.98
NormalL270.6766.6560.3784.1384.47
NormalL375.6568.8264.2085.2087.02
NormalL475.8566.1867.4385.7284.62
TowardL165.3067.8361.0387.5388.62
TowardL270.9066.5060.0791.1091.31
TowardL376.0369.8364.8392.0794.31
TowardL475.9067.2067.8092.5791.31

Want:

labelante_awaypost_awaymid_awayup_awaylow_awayante_normalpost_normalmid_normalup_normallow_normalante_towardpost_towardmid_towardup_towardlow_toward
L165.1366.6360.9376.0276.5266.0067.5061.5081.0481.9865.3067.8361.0387.5388.62
L269.9765.1360.2079.3779.1670.6766.6560.3784.1384.4770.9066.5060.0791.1091.31
L375.7768.1064.1778.8380.7675.6568.8264.2085.2087.0276.0369.8364.8392.0794.31
L475.1366.3068.2781.1877.4275.8566.1867.4385.7284.6275.9067.2067.8092.5791.31

Accepted Solutions
Solution
‎03-06-2014 06:11 PM
Super Contributor
Posts: 644

Re: Help with reshape the data with proc transpose

Proc Transpose data = have

                         out = step1

                         ;

     By     Int Label ;

Run ;

/* previous column name is in _NAME_ column */

Data Step2 ;

     Length _NAME_ $ 32 ;

     Set Step1 ;

          _Name_     =     cats('_', _NAME_, Int) ;

     Drop Int ;

Run ;

Proc Sort data = Step2 ;

     By     Label ;

Run ;

Proc Transpose data = step2

                         out = want (drop = _NAME_)

                         ;

     By     Label ;

Run ;

(untested)

Richard

View solution in original post


All Replies
Solution
‎03-06-2014 06:11 PM
Super Contributor
Posts: 644

Re: Help with reshape the data with proc transpose

Proc Transpose data = have

                         out = step1

                         ;

     By     Int Label ;

Run ;

/* previous column name is in _NAME_ column */

Data Step2 ;

     Length _NAME_ $ 32 ;

     Set Step1 ;

          _Name_     =     cats('_', _NAME_, Int) ;

     Drop Int ;

Run ;

Proc Sort data = Step2 ;

     By     Label ;

Run ;

Proc Transpose data = step2

                         out = want (drop = _NAME_)

                         ;

     By     Label ;

Run ;

(untested)

Richard

Occasional Contributor
Posts: 10

Re: Help with reshape the data with proc transpose

Hi Richard,

Thank you so much for your quick response. It worked, only a minor change of _Name_     =     cats( _NAME_, '_', Int) ;

The '_' and _NAME_ need to flip the position.

Thank you again,

Bo

Super Contributor
Posts: 644

Re: Help with reshape the data with proc transpose

Bo

Apologies - I meant to use the function catx, for which the parameter order would be as specified.  Your version is equally good - glad I could help.

Richard.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 201 views
  • 0 likes
  • 2 in conversation