BookmarkSubscribeRSS Feed
sas_
Fluorite | Level 6
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
3 REPLIES 3
SASJedi
SAS Super FREQ
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 ********************************;
Check out my Jedi SAS Tricks for SAS Users
Patrick
Opal | Level 21
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
sas_
Fluorite | Level 6
Thanks it worked

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 963 views
  • 0 likes
  • 3 in conversation