topic Re: Reformat data to be usable in SAS Procedures

Reformat data to be usable

Lani — Tue, 14 Feb 2012 02:44:14 GMT

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

jsample@email.com

--- empty row ---

BBB Inc

Jane Smith

456 Anywhere St.

Anytown, US 12345

jsmith@email.com

--- 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

Re: Reformat data to be usable

Haikuo — Tue, 14 Feb 2012 03:15:31 GMT

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

jsample@email.com

BBB Inc

Jane Smith

456 Anywhere St.

Anytown, US 12345

jsmith@email.com

;

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;

run;

proc print ;run;

Regards,

Haikuo

Re: Reformat data to be usable

Ksharp — Tue, 14 Feb 2012 09:07:40 GMT

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

Re: Reformat data to be usable

Lani — Wed, 15 Feb 2012 02:59:21 GMT

Thank you both. If there are more than one empty row between 2 records. How to read the data in and skip blank rows?

Re: Reformat data to be usable

art297 — Wed, 15 Feb 2012 04:18:50 GMT

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;

run;

Re: Reformat data to be usable

Tom — Wed, 15 Feb 2012 04:45:28 GMT

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;

Re: Reformat data to be usable

Lani — Wed, 15 Feb 2012 04:53:33 GMT

Thank you for all your help. You're all very helpful.

Re: Reformat data to be usable

Ksharp — Wed, 15 Feb 2012 09:00:20 GMT

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