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...
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;
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: 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.
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;
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...
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;
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;
I moved the whole thread to the Programming community.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.