I have multiple records in one observation separated by comma. how to convert in rows. EXAMPLE; data test; input name $50 id $200; datalines; A WE123-45, GYHJK-01, LLLLL-13, GGGGG-01 B AAAAA-50 BBBBB-03 ; RUN; WANT OUTPUT LIKE: A WE123-45 A GYHJK-01 A LLLLL-13 A GGGGG-01 B AAAAA-50 B BBBBB-03 please suggest!
Use the COUNTW function with a ',' delimiter to determine the number of occurences in a line. Then run a do loop for 1 to (result of COUNTW), use the SCAN function with delimiter ',' to get the nth value group, split that with another use of the SCAN function with default delimiter ' ', and output. KEEP only the wanted variables.
data test(drop=obs);
infile cards dlm = ',';
input obs :$1000. @@;
id=scan(obs,1,' ');
name=scan(obs,2,' ');
put id= name=;
cards;
A WE123-45,A GYHJK-01,A LLLLL-13,A GGGGG-01,B AAAAA-50,B BBBBB-03
;
run;
output:
id=A name=WE123-45
id=A name=GYHJK-01
id=A name=LLLLL-13
id=A name=GGGGG-01
id=B name=AAAAA-50
id=B name=BBBBB-03
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.
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.