BookmarkSubscribeRSS Feed
Sean_OConnor
Fluorite | Level 6

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. 

 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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

 

prefix=issue_

 

 

Ksharp
Super User

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;
Sean_OConnor
Fluorite | Level 6

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.

Astounding
PROC Star

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 711 views
  • 0 likes
  • 4 in conversation