BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Akaran
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Calcite | Level 5
Hi,
Thanks to solve this issue.
Regards,
Alok Karan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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