Help using Base SAS procedures

How to "ungroup" a grouped dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How to "ungroup" a grouped dataset

[ Edited ]

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!

 


Accepted Solutions
Solution
‎11-05-2015 09:06 PM
Super Contributor
Posts: 275

Re: How to "ungroup" a grouped dataset

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;

View solution in original post


All Replies
Solution
‎11-05-2015 09:06 PM
Super Contributor
Posts: 275

Re: How to "ungroup" a grouped dataset

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;

Occasional Contributor
Posts: 8

Re: How to "ungroup" a grouped dataset

Thank you so much! I appreciate your time Smiley Happy

Respected Advisor
Posts: 4,646

Re: How to "ungroup" a grouped dataset

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;

 

 

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 360 views
  • 0 likes
  • 3 in conversation