topic Re: Replicate every id by all possible unique group present in data in SAS Programming

Replicate every id by all possible unique group present in data

Miracle — Thu, 05 Aug 2021 14:25:04 GMT

Dear All,
Can I please seek your help on how to achieve this?
For example, I would like turn data X into data Y.

Thanks a lot.

Data X	Data Y
id grp	id grp
1 A	1 A
1 B	1 B
2 A	1 C
2 C	2 A
3 A	2 B
3 B	2 C
3 C	3 A
4 C	3 B
	3 C
	4 A
	4 B
	4 C

Re: Replicate every id by all possible unique group present in data

maguiremq — Thu, 05 Aug 2021 14:53:01 GMT

data x;
input id grp $1.;
datalines;
1 A
1 B
2 A
2 C
3 A
3 B
4 C
;

proc freq
	data = x noprint;
		tables id * grp / 	sparse 
							out = y (drop = count percent);
run;

id grp 
1 A 
1 B 
1 C 
2 A 
2 B 
2 C 
3 A 
3 B 
3 C 
4 A 
4 B 
4 C

I prefer the SPARSE statement with PROC FREQ to do things like this.

Re: Replicate every id by all possible unique group present in data

Miracle — Thu, 05 Aug 2021 15:00:57 GMT

Thanks @maguiremq for your quick reply.
I was thinking something like datastep by reading a macro variable containing all the unique grp then using do loop to repeat for every row but I dunno how exactly.
This is much easier and should do for now.