Desktop productivity for business analysts and programmers

Transposing rows to columns creates a variable name which is numeric

Reply
Contributor
Posts: 58

Transposing rows to columns creates a variable name which is numeric

Folks,

 

Can anyone provide any help with the following issue.

 

I have dataset which is based on rows of information that I need to transpose into columns. The issue is that rows look like this initially;

 

Rows
A1
A4
G5
X
4
5

 

However, when I transform the data I get the following;

 

A1A4G5X45

 

The all numeric values in the rows are actual errors so when I transpose them I'd like to fix any column which begins with a numeric value as issue_4. issue_5 etc. 

 

Any help would be great. 

 

Super User
Super User
Posts: 9,840

Re: Transposing rows to columns creates a variable name which is numeric

Posted in reply to Sean_OConnor

There is no option as far as I remember, but just do it in a datastep before:

data want;
  set have;
  if lengthn(compress(rows,'','kd))=0 then rows=cats("issue_",rows);
run;

Assumes length of rows is long enough.  Then transpose the result.

Super User
Posts: 6,933

Re: Transposing rows to columns creates a variable name which is numeric

Posted in reply to Sean_OConnor

Easiest would be to place "issue_" in front of all names, not just the numeric ones.  PROC TRANsPOSE supports this option:

 

prefix=issue_

 

 

Super User
Posts: 10,849

Re: Transposing rows to columns creates a variable name which is numeric

Posted in reply to Sean_OConnor

You can add prefix 'ISSUE_' before transposing it.

And make sure your X variable is long enough to hold 'issue_'.

 

data have;
input x $;
if notdigit(strip(x))=0 then x=cats('issue_',x);
cards;
A1
A4
G5
X
4
5
;
proc print;run;
Contributor
Posts: 58

Re: Transposing rows to columns creates a variable name which is numeric

Thanks K this works a treat. However, I left one key thing out in my opening post.

 

I also have a row with a value called 3X which creates issues also.

 

How would I amend the great piece of code below to search for any value which begins with number. Anything which starts with a number is incorrect e.g. A1 would be a valid entry but 1A wouldn't hence 1A would be changed to issue_1A.

 

Any input would be great.

Super User
Posts: 6,933

Re: Transposing rows to columns creates a variable name which is numeric

Posted in reply to Sean_OConnor

It's @Ksharp's idea, so give him the credit.  Here's a method I have been waiting for an opportunity to use:

 

if ('0' <=: strip(x) <=: '9') then x=cats('issue_',x);

Super User
Posts: 10,849

Re: Transposing rows to columns creates a variable name which is numeric

Posted in reply to Sean_OConnor

That would be simpler .

 

 

data have;
input x $;
if anydigit(strip(x))=1 then x=cats('issue_',x);
cards;
A1
A4
G5
X
4
5
3X 
;
proc print;run;
Ask a Question
Discussion stats
  • 6 replies
  • 126 views
  • 0 likes
  • 4 in conversation