topic Transposing rows to columns creates a variable name which is numeric in SAS Enterprise Guide

Transposing rows to columns creates a variable name which is numeric

Sean_OConnor — Tue, 06 Mar 2018 12:26:56 GMT

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

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

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.

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

RW9 — Tue, 06 Mar 2018 12:55:10 GMT

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.

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

Astounding — Tue, 06 Mar 2018 13:42:03 GMT

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

prefix=issue_

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

Ksharp — Tue, 06 Mar 2018 13:46:33 GMT

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;

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

Sean_OConnor — Tue, 06 Mar 2018 14:07:22 GMT

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.

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

Astounding — Tue, 06 Mar 2018 14:43:14 GMT

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);

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

Ksharp — Wed, 07 Mar 2018 10:18:10 GMT

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;