BookmarkSubscribeRSS Feed
Lani
Calcite | Level 5

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

7 REPLIES 7
Haikuo
Onyx | Level 15

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;

    end;

run;

proc print ;run;

Regards,

Haikuo

Ksharp
Super User

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

Lani
Calcite | Level 5

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

art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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;

Lani
Calcite | Level 5

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

Le

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 909 views
  • 0 likes
  • 5 in conversation