topic Re: Parse a long text field with varying length results to new variables in SAS Procedures

Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 17:40:29 GMT

I have a long text variable, already in a SAS dataset, that has this format (2 rows shown, I apologize for the lack of input statement, but with the semicolons (yes, really!) in there, I was not sure how to get them in as datalines):

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

Additional rows have more like that. Basically the part before the colon is text of some kind and many lengths, and the part after the colon is a number, each pair are separated by a semicolon. There are up to 13 pairs of text with a number in a given observation.

I want to be able to make new variables, splitting the field up at the colons and semicolons. This is the desired result for the example (and wow, cannot paste here?):

Sorry for the image, my browser does not seem to support my pasting here.

I have searched the discussion groups, tried various combinations of creating arrays, using the scan function. But those assume you know how may variables each row will create. I tried using call symput to assign the count of the number of : and ; to a macro variable, which could then be used to assign the dimension for the array for each row.

I could probably cop out and figure out my biggest set of pairs, use double that number as a dimension, figure out my longest new variable length, and then set all the new variable made from the array to the length.

I will keep looking online. I just thought I would ask. This is like excel's text to column function. I basically need to tell sas "split this long text field into new variables at the ; and : marks"

Thank you,

Nicole

Re: Parse a long text field with varying length results to new variables

PGStats — Wed, 13 Aug 2014 17:53:13 GMT

Would your first line have a different meaning if the order of label:value pairs was changed? I.e. if it was

bbbbbbbbb:12;aaaaaaa:10;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

Re: Parse a long text field with varying length results to new variables

Haikuo — Wed, 13 Aug 2014 17:56:28 GMT

Will something like this work for you?

data test;

infile cards truncover dsd dlm=';:';

input (var1-var8) (:$30.);

cards4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

Haikuo

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 18:05:35 GMT

PGStats, no, I do not think that it would make a difference. I provided dummy data. In the real world, the text is a department, and the number is the number of hours spent there. Although it would be convenient if Var1 was always the same department when the long field had the same ones in it. In other words, if another row starts with aaaaaaa:8, I would want the aaaaaaa to appear in Var1.

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 18:06:30 GMT

Thank you Hai.kuo. Infiling data is not my strong suit---most of my stuff comes from access or excel or sql server. And my data is already in a SAS dataset. I have to pull apart what I already have. Unless you think I should save it out to a text file and try infiling it back in?

But that will not work overall, because there can be up to 13 pairs (so 26 new variables created) and the length of the text portions can vary quite a bit.

Re: Parse a long text field with varying length results to new variables

Astounding — Wed, 13 Aug 2014 18:14:30 GMT

A variation on Hai.kuo's approach:

data want;

infile datalines missover dsd dlm=';:';

length var1 var3 var5 var7 var9 var11 var13 var15 var17 var19 var21 var23 var25 $ 256;

input var1-var26;

cards4;

... the data lines that you would like ...

;;;;

It's important to remove formatting instructions from the INPUT statement when scanning a line from left to right. When using the combination of (: $30.) the software searches for a non-delimiter and then reads the next 30 characters regardless of whether or not they contain a delimiter.

Good luck.

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 18:19:36 GMT

Thank you Astounding, for the response.

Maybe I am missing something.........I'm not sure why an infile statement would be appropriate. This long text variable is already in SAS. It is in a dataset that was provided to me by another group where I work--they created it from sql server. I do not have access to the "raw" data. And there are a bunch of other variables in the sas dataset, so I am really just looking to parse this long field into new variables.

I literally have a 600 length field with one long string in it. The string is text and numbers, separated by : and ; as indicated above.

Is the only way to do this to export it and bring it back in? I was thinking there might be a more dynamic way to use what is already there.

Re: Parse a long text field with varying length results to new variables

PGStats — Wed, 13 Aug 2014 18:21:49 GMT

I would do something like this:

data have;

length text $200;

input text;

datalines4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

data list;

length pair $64 label $24;

set have;

line + 1;

do i=1 to countw(text,";");

pair = scan(text,i,";");

label = scan(pair, 1, ":");

if not missing(label) then do;

value = input(scan(pair, 2, ":"), best.);

output;

end;

keep line label value;

run;

proc transpose data=list out=want(drop=_name_);

by line;

id label;

var value;

run;

Re: Parse a long text field with varying length results to new variables

Haikuo — Wed, 13 Aug 2014 18:22:42 GMT

Another shot:

data have;

infile cards truncover ;

input var $300.;

cards4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

data want1;

set have;

n=_n_;

do _n_=1 by 1 while (not missing (scan(var,_n_,':;')));

temp=scan(var,_n_,':;');

output;

end;

run;

proc transpose data=want1 out=want (drop=n _name_);

by n;

var temp;

run;

This should be dynamic, you can play with prefix option if you are not happy with the default 'col's

Good luck,

Haikuo

Re: Parse a long text field with varying length results to new variables

RaviKommuri — Wed, 13 Aug 2014 18:30:57 GMT

I have tried this static code.....

data have;

infile datalines missover;

input text $63.;

datalines4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

data want(drop=text);

set have;

array var(8) $ var1-var8;

do i=1 to 8;

var(i) = scan(text,i,':;');

end;

Ofcourse there are better solutions above..... 🙂

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 18:33:48 GMT

PGStats--thank you, I will attempt to apply that code to my situation, and report back on how it works. However, if my initial reading of your code is correct, it still requires me to go in and figure out the length of my future variables ahead of time. so I can set the length of pair and label. Again, I was hoping for a way for that to by determined dynamically. But I guess, in thinking about it, that is not realistic, since SAS requires us to either set the length, or uses the first value received to set it.

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 18:36:34 GMT

Thanks Haikuo--I will try yours too. I appreciate the support!

Re: Parse a long text field with varying length results to new variables

Astounding — Wed, 13 Aug 2014 19:08:47 GMT

No, I was the one who was missing something. Here's part 1, determine the lengths needed.

data _null_;

retain max_pair max_label 0;

set have end=done;

i=0;

length next_piece $2000;

do until (next_piece=' ');

i+1;

next_piece = scan(long_var, i, ';:');

if mod(i,2)=1 then max_pair = max(max_pair, length(next_piece));

else max_label = max(max_label, length(next_piece));

end;

if done;

call symputx('max_pair', max_pair);

call symputx('max_label', max_label);

run;

That gives you macro variables holding the length needed for the section before the colon (&max_pair) and the section after the colon (&max_label). They could be used in a later program in various ways, such as:

array pairs {13} $ &max_pair;

Do you need a more specific solution than that? For example, in your diagram, would you like VAR1 and VAR3 to have the same length, or should we assign separate lengths to each? (Or doesn't it matter?)

For part 2, I was unsure of the proper result. Are you guaranteed a maximum of 13 pairs per observation? Do you want to keep the data in its current structure as in your original diagram? The alternative would be to output each pair as a separate observation. Either is possible ... just want to make sure I solve the right problem.

Sorry for all the questions, but they do affect the solution. And there definitely are possible solutions.

Re: Parse a long text field with varying length results to new variables

NWV — Wed, 13 Aug 2014 19:33:23 GMT

Haikuo and PGStats, thank you, you both were very close to what I needed. Haikuo--I like that I was able to use yours without defining the lengths ahead of time--although I am still checking to make sure there was no truncation due to something I missed. And it is a little frustrating to have to essentially take this long variable out of the dataset, parse it, and transpose it, and join it back to the original dataset. But it is possible, since I have a unique ID to transpose by.

Astounding......I am intrigued by your option, and I will see if I can apply at least the first part. I had tried assigning a macro variable via call symput (using a countc of ; and : ) and applying that to the array statement, but SAS was pretty insistent that I could not use the macro variable in setting up the array---but it may have been the way I was applying it. The lengths of the numeric parts don't matter (as long as the entire number comes in), in the end I will be inputing them to numeric variables. And the character variables need to be large enough to accommodate whatever text ends up being pieced into them--so as dynamic as possible. And yes, I want the data to appear as in the diagram--simply var1-varx, with the text followed by the number. This has to do with the type of analysis that it will be used for down the road--a job exposure matrix, where we need the department and time spent on one line associated with a specific employee.

Thank you all!!!!!

Re: Parse a long text field with varying length results to new variables

slchen — Wed, 13 Aug 2014 20:02:33 GMT

data have(keep=var);

infile cards truncover dlm=';:';

input (temp1-temp100)($);

array temp temp:;

do i=1 to 100;

var=temp(i);

if not missing(var) then output;

else leave;

end;

cards4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

Re: Parse a long text field with varying length results to new variables

PGStats — Wed, 13 Aug 2014 20:25:51 GMT

My code doesn't generate any character variable. The labels become the names of numeric variables (they are limited to 32 characters by SAS naming rules). Intermediate character variables pair and label can be given a rather large size since they are not kept in the end.

Re: Parse a long text field with varying length results to new variables

NWV — Thu, 14 Aug 2014 12:03:22 GMT

Ok, thank you for clarifying PGStats. The statistician actually wants the character part in a variable, not as a name of the numeric variables. Like I pointed out in my original post......that is the desired format (although there will be more fields). I am going to keep your code on hand though, as there are times when they do want the character portion to label the numeric. :smileygrin:

Re: Parse a long text field with varying length results to new variables

Tom — Sun, 17 Aug 2014 03:52:49 GMT

If you want to know how many departments and the maximum length of any department's name then you can just count.

data have ;

input str $80. ;

card4;

aaaaaaa:10;bbbbbbbbb:12;ccccccccccccc:2;jkjkjkjkjkjkjkjkjk:40;

xxxxxx:23;ghghghghghghghghghgh:10;

;;;;

data _null_;

set have end=eof ;

retain nvar 0 len 0;

nvar=max(nvar,countw(str,';')-1);

do i=1 to countw(str,';')-1;

len=max(len,length(scan(scan(str,i,';'),1,':')));

end;

if eof then call symputx('nvar',nvar);

if eof then call symputx('len',len);

run;

data want ;

set have ;

array dept (&nvar) $&len ;

array hours (&nvar) ;

do _n_=1 to countw(str,';')-1;

dept(_n_)=scan(scan(str,_n_,';'),1,':');

hours(_n_)=input(scan(scan(str,_n_,';'),2,':'),32.);

end;

drop str;

run;

proc print;

run;

Re: Parse a long text field with varying length results to new variables

NWV — Mon, 18 Aug 2014 12:51:15 GMT

Thanks Tom, that is some helpful code as well. I will save it, along with the other, in case they decide they want to display the data as department1-departmentN and hours1-hoursN. Right now they want it as field1-fieldN with the department in the first one and the hours in the next. Your code also clarifies the use of call symputx--I had at first tried call symput, but then when I tried to use that as a parameter in the array, SAS gave me an error message. Thanks again!