03-06-2018 07:26 AM
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;
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.
03-06-2018 07:55 AM
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.
03-06-2018 08:42 AM
Easiest would be to place "issue_" in front of all names, not just the numeric ones. PROC TRANsPOSE supports this option:
03-06-2018 08:46 AM
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;
03-06-2018 09:07 AM
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.
03-06-2018 09:43 AM
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);
03-07-2018 05:18 AM
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;