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!
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;
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;
Thank you so much! I appreciate your time 🙂
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.