topic Re: observation separated by comma. How to convert in rows. in SAS Programming

observation separated by comma. How to convert in rows.

P_Sharma — Thu, 27 Mar 2014 07:03:17 GMT

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!

Re: observation separated by comma. How to convert in rows.

Kurt_Bremser — Thu, 27 Mar 2014 08:11:21 GMT

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.

Re: observation separated by comma. How to convert in rows.

pradeepalankar — Thu, 27 Mar 2014 08:53:03 GMT

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