DATA Step, Macro, Functions and more

text to column

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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:

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
Super User
Posts: 7,980

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.

View solution in original post


All Replies
Solution
‎03-04-2016 04:48 AM
Super User
Super User
Posts: 7,980

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: 5,516

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.

New Contributor
Posts: 2

Re: text to column

Hi,
Thanks to solve this issue.
Regards,
Alok Karan
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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