topic Re: Expanding Excel Cell into Rows and Filling in Other Columns in SAS Programming

Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 14:54:45 GMT

How do you Convert this Excel row:

Test1

Test1.1

123, 124, 125, 521, 324, 432, 632, 992, 214, 215

Into these Excel rows:

Test1	Test1.1	123
Test1	Test1.1	124
Test1	Test1.1	125
Test1	Test1.1	521
Test1	Test1.1	324
Test1	Test1.1	432
Test1	Test1.1	632
Test1	Test1.1	992
Test1	Test1.1	214
Test1	Test1.1	215

Re: Expanding Excel Cell into Rows and Filling in Other Columns

novinosrin — Thu, 04 Oct 2018 15:09:32 GMT

data have;
infile cards dlm=', ';
input (t1-t2) ($) t3- t12;
cards;
Test1 Test1.1 123, 124, 125, 521, 324, 432, 632, 992, 214, 215
;

proc transpose data=have out=want(drop=_name_);
by t1 t2;
var t3-t12;
run;

PS I am assuming you want to do that in SAS and you are posting accordingly in the right forum and not MS Excel forum?

Re: Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 15:22:19 GMT

Yes done within SAS. Can you show me how it's done within importing an excel data. It's a large file.

Re: Expanding Excel Cell into Rows and Filling in Other Columns

novinosrin — Thu, 04 Oct 2018 15:29:07 GMT

Are you asking how to import an excel file to SAS?

If yes, have you looked into

1.proc import

2. using import wizard point and click interface

3. libname engiine xls

and so on.

You may have to search online quite a bit and do some reading

Re: Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 15:34:12 GMT

No I have the data imported and I'm wondering how to manipulate the data within the data step to expand the excel cell.

Re: Expanding Excel Cell into Rows and Filling in Other Columns

novinosrin — Thu, 04 Oct 2018 15:36:46 GMT

Aah ok,

data have;
infile cards dlm=', ';
input (t1-t2) ($) t3- t12;
cards;
Test1 Test1.1 123, 124, 125, 521, 324, 432, 632, 992, 214, 215
;

data want;
set have;
array j t3-t12;
do over j;
want=j;
output;
end;
keep t1 t2 want;
run;

Re: Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 15:42:08 GMT

The data is more complicated than that. Here's a better sample of the data. This goes on for 40,000+ excel lines.

Name1	Name1.1	99201, 99202, 99203, 99204, 99205, 97813, 97814, 97810, 97811
Name1	Name1.2	99205, 99211, 97813, 97814, 97810, 97811
Name2	Name2.1	S5101, S5102, S5105

Re: Expanding Excel Cell into Rows and Filling in Other Columns

novinosrin — Thu, 04 Oct 2018 15:48:43 GMT

Oh well, i see your num values are all in a one variable seperated by comma as delimiter

Alll you need to do is scan one by one in a loop and output for each scan.

data have;
infile cards truncover;
input t1 $ t2 $ num  $100.;
cards;
Name1	Name1.1	99201, 99202, 99203, 99204, 99205, 97813, 97814, 97810, 97811
Name1	Name1.2	99205, 99211, 97813, 97814, 97810, 97811
Name2	Name2.1	S5101, S5102, S5105
;

data want;
set have;
k=countw(num,',');
do i=1 to k;
want=scan(num,i,',');
output;
end;
keep t: want;
run;

Re: Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 15:55:53 GMT

Didn't work.

Re: Expanding Excel Cell into Rows and Filling in Other Columns

novinosrin — Thu, 04 Oct 2018 15:57:02 GMT

I can only test with the sample you post. So you may have to adjust to your real data accordingly.

Re: Expanding Excel Cell into Rows and Filling in Other Columns

alexjaco — Thu, 04 Oct 2018 16:06:06 GMT

I figured it out! Thanks for your help!