DATA Step, Macro, Functions and more

observation separated by comma. How to convert in rows.

Reply
Occasional Contributor
Posts: 16

observation separated by comma. How to convert in rows.

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!

Super User
Posts: 7,782

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 106

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 197 views
  • 4 likes
  • 3 in conversation