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.
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.
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.