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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.