Hello~
I have an excel file containing multi records. Each record has company, name, address, city/state/zip, and email info but they are in one column.
For example:
AAA Inc
John Sample
123 Anywhere St.
Anytown, US 12345
--- empty row ---
BBB Inc
Jane Smith
456 Anywhere St.
Anytown, US 12345
--- empty row ---
and so on....
I need to reformat the data so that each info is in each colum:
Company Name Address City_state_zip Email
AAA Inc John Sample 123 Anywhere St Anytown, US 12345 jsample@email.com
BBB Inc Jane Smith 456 Anywhere St Anytown, US 12345 jsmith@email.com
...
Thank you. It's much appreciated for your input.
Lani
I am sure someone will come up slicker solutions, for the time being, you could try the following code. But first, you need to use Proc import or Libname or menu driven method etc. to import your excel to SAS table.
data have;
infile cards;
input _have $50.;
cards;
AAA Inc
John Sample
123 Anywhere St.
Anytown, US 12345
BBB Inc
Jane Smith
456 Anywhere St.
Anytown, US 12345
;
data want (drop=_:);
length Company $50
Name $50
Address $50
City_state_zip $50
Email $50;
do _n_=1 to 6;
set have;
if _n_=1 then company=_have;
else if _n_=2 then name=_have;
else if _n_=3 then address=_have;
else if _n_=4 then city_state_zip=_have;
else if _n_=5 then
do;
email=_have;
output;
end;
end;
run;
proc print ;run;
Regards,
Haikuo
How about:
data have; infile cards length=len; input have $varying200. len; cards; AAA Inc John Sample 123 Anywhere St. Anytown, US 12345 jsample@email.com BBB Inc Jane Smith 456 Anywhere St. Anytown, US 12345 jsmith@email.com ; run; data want(drop=have); set have; length Company Name Address City_state_zip Email $ 200; retain Company Name Address City_state_zip Email; select(mod(_n_,6)); when(1) Company=have; when(2) Name=have; when(3) Address=have; when(4) City_state_zip=have; when(5) Email=have; when(0) output; otherwise; end; run;
Ksharp
Tian.Kong
Thank you both. If there are more than one empty row between 2 records. How to read the data in and skip blank rows?
Le
Institute your own counter rather than use _n_. e.g.,
data want(drop=have counter);
set have;
length Company Name Address City_state_zip Email $ 200;
retain Company Name Address City_state_zip Email;
if strip(have) ne '' or counter gt 1 then do;
counter+1;
select(mod(counter,6));
when(1) Company=have;
when(2) Name=have;
when(3) Address=have;
when(4) City_state_zip=have;
when(5) Email=have;
when(0) do;
output;
counter=0;
end;
otherwise;
end;
end;
run;
What is the problem? The example data seems straight forward. If you really always have 5 lines of data per record then a single input statement will do. If you have a variable number of blank records then a loop should handle that.
data want ;
infile tmpfile1 truncover end=eof ;
input company $200. / name $200. / address $200. / city $200. / email $200. @ ;
if not eof then input / @@;
do while (_infile_ = ' ' );
if eof then stop;
input / @@;
end;
run;
Thank you for all your help. You're all very helpful.
Le
How about
data have; infile cards length=len; input have $varying200. len; cards; AAA Inc John Sample 123 Anywhere St. Anytown, US 12345 jsample@email.com BBB Inc Jane Smith 456 Anywhere St. Anytown, US 12345 jsmith@email.com ; run; data want(drop=have ); set have(where=(have is not missing)); length Company Name Address City_state_zip Email $ 200; retain Company Name Address City_state_zip Email; select(mod(_n_,5)); when(1) Company=have; when(2) Name=have; when(3) Address=have; when(4) City_state_zip=have; when(0) do;Email=have;output;end; otherwise; end; run;
Ksharp
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.