Solved: Re: text to column

Akaran · Posted 03-03-2016 05:34 AM

Hi All,

I stuck in one of the problem while extracting data from xlsx. my original data and desired data is like below

Original data&colon;

Party Id	Company Name	Fortune 1000 Ranking	Address Line 1	City	State Or Province	Postal Code
228732334, 221772380, 221771055, 41600426, 10043	Berkshire Hathaway Inc.	4	3555 Farnam St	Omaha	Nebraska	68131-3311

Desired Data

can anyone plz help on this issue

RW9 · Posted 03-03-2016 05:52 AM

Hi,

Basically you just want to repeat the row for each comma separated value in column 1 yes? if so:

data want;
  set have;
  length party_id_calc $200;
  do i=1 to countw(partyid,",");
    party_id_calc=scan(partyid,i,",");
    output;
  end;
run;

Not tested as no test data (in the form of a datastep), but something like that should work.

RW9 · Posted 03-03-2016 05:52 AM

Hi,

Basically you just want to repeat the row for each comma separated value in column 1 yes? if so:

data want;
  set have;
  length party_id_calc $200;
  do i=1 to countw(partyid,",");
    party_id_calc=scan(partyid,i,",");
    output;
  end;
run;

Not tested as no test data (in the form of a datastep), but something like that should work.

Astounding · Posted 03-03-2016 09:04 AM

This approach looks good, but I would recommend a minor change. Include blanks as a delimiter in the SCAN function:

party_id_calc=scan(partyid,i,", ");

Otherwise, you can get leading blanks as a part of the new variable.

Akaran · Posted 03-04-2016 04:50 AM

Hi,
Thanks to solve this issue.
Regards,
Alok Karan

text to column