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

[NWV]

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!!!!!

[slchen]

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;

;;;;

[Tom]

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;

[NWV]

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!