BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Brijesh4sas
Fluorite | Level 6

Input
Pin       Address;
75008  Paris 8è
            Les Ulis Essonne
92330  Sceaux Hauts-de-Seine
93140  Bondy Seine-Saint-Denis
            Rungis

Expected Output

Pin        Address;
75008   Paris 8è Les Ulis Essonne
92330  Sceaux Hauts-de-Seine
93140  Bondy Seine-Saint-Denis Rungis

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
infile cards truncover;
input Pin       Address $40.;
cards;
75008  Paris 8è
    .        Les Ulis Essonne
92330  Sceaux Hauts-de-Seine
93140  Bondy Seine-Saint-Denis
   .         Rungis
;
data temp;
 set have;
 if not missing(pin) then group+1;
run;
data want;
 do until(last.group);
  set temp;
  by group;
  length want $ 400;
  if first.group then want_pin=pin;
  want=catx(' ',want,address);
 end;
 keep want_pin want;
run;

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

What format is your source?  Is it a text file? Something else?

Brijesh4sas
Fluorite | Level 6
It could be text or dataset
mkeintz
PROC Star

This is a case for reading two lines at a time, but to hold the second line for re-reading.  I.e. each line gets read twice: once to see if it should have its address concatenated with the prior (primary) line, and once as the primary line:

 

data want (drop=nxt_:);
  input @1 PIN $5.     @8 address &$40. 
      / @1 nxt_pin $5. @8 nxt_address &$20.  @@;

  if nxt_pin=' ' then address=catx(' ',address,nxt_address);
  if pin^=' ';
datalines;
75008  Paris 8è
       Les Ulis Essonne
92330  Sceaux Hauts-de-Seine
93140  Bondy Seine-Saint-Denis
       Rungis
run;

The double trainling @@ tells sas not to throw away the second line, so that it is available for re-reading when serving as the primary line.  

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
data have;
infile cards truncover;
input Pin       Address $40.;
cards;
75008  Paris 8è
    .        Les Ulis Essonne
92330  Sceaux Hauts-de-Seine
93140  Bondy Seine-Saint-Denis
   .         Rungis
;
data temp;
 set have;
 if not missing(pin) then group+1;
run;
data want;
 do until(last.group);
  set temp;
  by group;
  length want $ 400;
  if first.group then want_pin=pin;
  want=catx(' ',want,address);
 end;
 keep want_pin want;
run;
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1407 views
  • 1 like
  • 4 in conversation