How to "ungroup" a grouped dataset

[willy06251]

Hi SAS experts

I would like to know how to manipulate the below data where the

distinct_id is numeric

prog is string

client_id is string

DISTINCT_ID	prog	CLIENT_ID
0	H	1101,1102
1	C	1110
1	D	1150
1	H	1170,1175,1154

so that I end up with below where client_id is now numeric

DISTINCT_ID	prog	CLIENT_ID
0	H	1101
0	H	1102
1	C	1101
1	D	1150
1	H	1170
1	H	1175
1	H	1154

So basically, upgrouping the datasets and deriving the transaction dataset.

I am aware that you normally go the other way around so I am finding this tricky.

How do you achieve this?

Thanks so much in advance!

[slchen · Accepted solution]

data have;
input DISTINCT_ID prog $ CLIENT_ID $20.;
cards;
0 H 1101,1102
1 C 1110
1 D 1150
1 H 1170,1175,1154
;

data want(rename=(var=client_id));
set have;
do i=1 by 0;
var=scan(client_id,i);
if missing(var) then return;
i+1;
output;
end;
drop i client_id;
run;

[willy06251]

Thank you so much! I appreciate your time 🙂

[PGStats]

Keeping it simple:

data want;
set have;
do i = 1 to countw(client_id);
    id = input(scan(client_id, i), best.);
    output;
    end;
drop client_id i;
rename id=client_id;
run;