Multiple record into Single record

[vThanu]

if you look into the resulted output (want dataset)

Name	Landline	Mobile
a	1234678	991122488
b	8795643210	77889912
c	4085794	77889912
d	9112456781	77889912

In record c & d mobile no of b record is reating.... which shd be supressed and should be display as . (period) or blank?

and one more thing is all 8 digits numbers should come under Landline and 10 digits number should be under Mobile no...

[vThanu]

Hello,

Plz can any one help me how to read mutliple records into single record on conditionally

data have;

input name$ contact 10.;

datalines;

a 1234678

a 9911224488

b 8795643210

c 04085794

d 9112456781

b 77889912

;

and looking for the below result. (if the name appears twice and have muliple records should be write in single record in the result like below)  

Name$  landline             Mobile

a           1234678            9911224488

b           77889912           8795643210

c           04085794                             .

d                           .         9112456781

[learsaas]

[Kurt_Bremser]

@learsaas: posting code in pictures is very unhelpful.

It forces others to type everything in manually, wasting time and giving ample opportunity for mistakes.

Use the "little running man" icon or the {i} button to post code by simply using copy/paste from the program editor.

The "little running man" will do its best to mimic the coloring of the Enhanced Editor.

[Shmuel]

You can either use proc transpose to get the wanted dataset from the having one or

define an array of contacts and enter each contact to separate member of the array.

Assuming data is sorted by name:

data have;
input name$ contact 10.;
datalines;
a 1234678
a 9911224488
b 8795643210
c 04085794
d 9112456781
b 77889912
;
run;

data want;
 set have;
  by name;
  retain i contact1-contact9; /* adapt to max number of contacts expected */
  drop i contact;
  array cn {*} contact1-contact9 ;

  if first.name then i=1;
  cn(i) = contact; 
  i+1;
  if last.name then output;
run;
 

[vThanu]

data want (keep=name contact1 contact2);
set have;
by name; retain i contact1-contact9; /* adapt to max number of contacts expected */
drop i contact;
array cn {*} contact1-contact9 ;
if first.name then i=1;
cn(i) = contact;
i+1;
if last.name then output;
run;

Name	Contact1	Contact2
a	1234678	991122488
b	8795643210	77889912
c	4085794	77889912
d	9112456781	77889912

The resulted output showing repeated values for record C and record D of record B values.

Ideally it should be . (period) or blank...

[RW9]

data want (drop=contact);
  set have;
  by name;
  retain landline mobile;
  if first.name then landline=contact;
  if last.name and not first.name then mobile=contact;
  if last.name then output;
run;

[Kurt_Bremser · Accepted solution]

If you want to reliably use the number of digits to make decisions, you need to work with character variables, and make use of the tools SAS provides for converting long datasets to wide:

data have;
input name $ contact :$10.;
if length (contact) = 10
then type = 'mobile  ';
else type = 'landline';
datalines;
a 1234678
a 9911224488
b 8795643210
c 04085794
d 9112456781
b 77889912
;
run;

proc sort data=have;
by name;
run;

proc transpose
  data=have
  out=want (drop=_name_)
;
by name;
var contact;
id type;
run; 

[Kurt_Bremser]

I moved the whole thread to the Programming community.