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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.