DATA Step, Macro, Functions and more

Reg:Separation and moving to another Line

Reply
Contributor
Posts: 66

Reg:Separation and moving to another Line

i am having data having A_NEW DATA SET WITH comma IN NAME AND AGE VARIABLES.

DATA A_NEW;
INPUT id$ no$ name$ age$;
CARDS;
3 10 a,b,c 10,11,12
2 11 e,f,g 11,15,16
12 12 w,r,t 1,2,3
14 13 p,m 4,5
15 14 a,b,c,d,e 1,2,3,4,5
RUN;

vARIABLES NAME IS HAVING VALUES WITH COMMAS IN IT AND AGE IS ALSO HAVING COMMAS IN IT

i want the output in like this,but i dont know the number of commas ARE THERE AS THE NAMES VARIABLE IS HAVING 3 COMMA OR 5 OR 6 COMMAS IT SHOULD BE MOVED TO ANOTHER LINES BASED ON THAT COMMAS AS I AM HAVING HUGE DATA

i WANT TO CREATE DATA SET B_NEW LIKE THIS

DATASET B_NEW Output:

id no name age
3 10 a 10
3 10 b 11
3 10 c 12
2 11 e 11
2 11 f 15
2 11 g 16
12 12 w 1
12 12 r 2
12 12 t 3
14 13 p 4
14 13 m 5
15 14 a 1
15 14 b 2
15 14 c 3
15 14 d 4
15 14 e 5
SAS Employee
Posts: 104

Re: Reg:Separation and moving to another Line

Try something like this:
** Begin code ********************************;
data test;
input ID NO Name:$1;
Names=scan(_infile_,3,' ');
Ages=scan(_infile_,4,' ');
do i= 1 to 100;
name=scan(Names,i,',');
if name ne '' then do;
age=input(scan(Ages,i,','),best.);
output;
end;
else leave;
end;
drop i names ages;
datalines;
3 10 a,b,c 10,11,12
2 11 e,f,g 11,15,16
12 12 w,r,t 1,2,3
14 13 p,m 4,5
15 14 a,b,c,d,e 1,2,3,4,5
;
run;

** End code ********************************;
Respected Advisor
Posts: 3,896

Re: Reg:Separation and moving to another Line

data A_NEW(drop=nameIn ageIn i);
  infile datalines truncover dsd dlm=' ';
  INPUT id$ no$ nameIn$ ageIn$;
  i=1;
  do until( scan(nameIn,i,',') eq '' );
    name=scan(nameIn,i,',');
    age=scan(ageIn,i,',');
    output;
    i+1;
    end;
    datalines;
3 10 a,b,c 10,11,12
2 11 e,f,g 11,15,16
12 12 w,r,t 1,2,3
14 13 p,m 4,5
15 14 a,b,c,d,e 1,2,3,4,5
;
run;


HTH
Patrick
Contributor
Posts: 66

Re: Reg:Separation and moving to another Line

Thanks it worked
Ask a Question
Discussion stats
  • 3 replies
  • 172 views
  • 0 likes
  • 3 in conversation