topic Re: text to column in SAS Programming

text to column

Akaran — Thu, 03 Mar 2016 10:34:57 GMT

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&colon;

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

can anyone plz help on this issue

RW9 — Thu, 03 Mar 2016 10:52:18 GMT

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 — Thu, 03 Mar 2016 14:04:08 GMT

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 — Fri, 04 Mar 2016 09:50:31 GMT

Hi,
Thanks to solve this issue.
Regards,
Alok Karan