topic Re: Multiple record into Single record in SAS Programming

Re: Multiple record into Single record

vThanu — Wed, 12 Dec 2018 08:07:56 GMT

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

Multiple record into Single record

vThanu — Tue, 11 Dec 2018 06:55:22 GMT

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

Re: Multiple record into Single record

learsaas — Tue, 11 Dec 2018 07:15:29 GMT

Re: Multiple record into Single record

Shmuel — Tue, 11 Dec 2018 07:18:52 GMT

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;

Re: Multiple record into Single record

Kurt_Bremser — Tue, 11 Dec 2018 08:29:46 GMT

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

Re: Multiple record into Single record

RW9 — Tue, 11 Dec 2018 09:03:17 GMT

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;

Re: Multiple record into Single record

vThanu — Wed, 12 Dec 2018 08:16:00 GMT

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

Re: Multiple record into Single record

Kurt_Bremser — Wed, 12 Dec 2018 08:25:25 GMT

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;

Re: Multiple record into Single record

Kurt_Bremser — Wed, 12 Dec 2018 08:28:04 GMT

I moved the whole thread to the Programming community.