Expanding Excel Cell into Rows and Filling in Other Columns

[alexjaco]

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

[novinosrin]

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? 

[alexjaco]

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

[novinosrin]

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

[alexjaco]

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

[novinosrin]

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;

[alexjaco]

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

[novinosrin]

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;

[alexjaco]

Didn't work.

[novinosrin]

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

[alexjaco]

I figured it out! Thanks for your help!