## text to column

Solved
Occasional Contributor
Posts: 5

# text to column

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

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

can anyone plz help on this issue

Accepted Solutions
Solution
‎03-04-2016 04:48 AM
Super User
Posts: 9,599

## Re: text to column

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.

All Replies
Solution
‎03-04-2016 04:48 AM
Super User
Posts: 9,599

## Re: text to column

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.

Super User
Posts: 6,785

## Re: text to column

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.

Occasional Contributor
Posts: 5

## Re: text to column

Hi,
Thanks to solve this issue.
Regards,
Alok Karan
🔒 This topic is solved and locked.

Discussion stats
• 3 replies
• 288 views
• 1 like
• 3 in conversation