Help using Base SAS procedures

Reg Comma and output

Reply
Contributor
Posts: 66

Reg Comma and output

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
Super User
Posts: 9,681

Re: Reg Comma and output

OK.
Since SAS has such power to do it.
[pre]


data want;
length id name age $ 8 ;
infile datalines length=len;
input row $varying100. len;
id=scan(row,1,' ,');
do i=1 to 3;
name=scan(row,i+1,' ,');
age=scan(row,i+4,' ,');
output;
end;
drop row i;
datalines;
3 a,b,c 10,11,12
2 e,f,g 11,15,16
12 w,r,t 1,2,3
;
run;
[/pre]


Ksharp
Super User
Posts: 9,681

Re: Reg Comma and output

OK.It is easy.
[pre]



data want(where=( age is not missing));
length id no $ 8 name age $ 200;
infile datalines length=len;
input row $varying100. len;
id=scan(row,1,' ,');no=scan(row,2,' ,');
i=1;
do until(missing(name) or missing(age)) ;
name=scan(scan(row,-2,' '),i,',');
age=scan(scan(row,-1,' '),i,',');
output;
i+1;
end;
drop row i;
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;
[/pre]


Ksharp
Ask a Question
Discussion stats
  • 2 replies
  • 129 views
  • 0 likes
  • 2 in conversation