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
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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.