Help using Base SAS procedures

Reformat data to be usable

Reply
Contributor
Posts: 22

Reformat data to be usable

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

Respected Advisor
Posts: 3,124

Re: Reformat data to be usable

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=_Smiley Happy;

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

Super User
Posts: 9,682

Re: Reformat data to be usable

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

Contributor
Posts: 22

Re: Reformat data to be usable

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

PROC Star
Posts: 7,363

Re: Reformat data to be usable

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;

Super User
Super User
Posts: 6,502

Re: Reformat data to be usable

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;

Contributor
Posts: 22

Re: Reformat data to be usable

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

Le

Super User
Posts: 9,682

Re: Reformat data to be usable

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

Ask a Question
Discussion stats
  • 7 replies
  • 195 views
  • 0 likes
  • 5 in conversation