topic Re: How to create table on this data? in SAS Programming

How to create table on this data?

Ganeshk — Wed, 28 Dec 2016 19:24:29 GMT

Hi,

I have a data as shown in below table:

ID	Group	Subgroup
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX

From the above table i need to obtain Summary based on Group wise and Subgroup wise.

Note: Within in the group there must be unique Id on overall. EX:101 ID is present in both "AB" and "GH" Subgroup in Group "A". In this case we need to consider only "AB" Subgroup and the other one "GH" can be 0

ID	Group	Subgroup
101	A	AB
102	A	CD
103	A	EF
0	A	GH
104	A	IJ
0	A	KL
501	B	MN
521	B	OP
531	B	QR
0	B	ST
0	B	UV
540	B	WX

Kindly note: ID 101 can be more than twice among the group.

Thanks,

Ganesh K

Re: How to create table on this data?

LinusH — Wed, 28 Dec 2016 19:39:05 GMT

Simply put: you wish to replace the if with a 0 whenan id is repeated within a group?
Without knowing the business requirement behind it seem like an odd operation, especially when looking from a data management perspective.
It can be done, resort the data by id an group and reassign id in a data step using by-group processing (hint: if not first.id then...)

Re: How to create table on this data?

mkeintz — Wed, 28 Dec 2016 21:46:17 GMT

Your data is apparently sorted by group/subgroup, and you want to reset ID to 0 for any id that has already appeared within the group/subgroup. As @LinusH said, it's not clear to me why one would do this (as opposed to, say, adding a second variable indicating a given id has already been present), but here is a way to do it in one step:

data want;
  set have;
  by group subgroup;

  length list $2000;
  retain list ' ';
  if first.group then list='';

  if findw(list,cats(id),',') then id=0;  /* Finds a word within a string with comma separators*/
  else list=catx(',',list,id);
  drop list;
run;

Notes:

Dataset HAVE is assumed to be already sorted by group/subgroup.
The cats(id) function might seem unnecessary (?concatenating a variable to itself?). It's advantage here is that it converts numeric to character data without generating an error message.

Re: How to create table on this data?

Ksharp — Thu, 29 Dec 2016 04:36:47 GMT

data have;
infile cards truncover expandtabs;
input ID	Group $ Subgroup $;
cards;
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX
;
run;
data have;
 set have;
 array x{999999} _temporary_;
 if id not in x then do;n+1;x{n}=id;end;
  else id=0;
run;

Re: How to create table on this data?

mkeintz — Thu, 29 Dec 2016 05:30:35 GMT

@Ksharp

I think the OP wants within group unique id's, not neccessarily unique overall.

Re: How to create table on this data?

Ksharp — Thu, 29 Dec 2016 13:55:00 GMT

Opps. I must browse it too quickly .

Fixed a typo .

data have;
infile cards truncover expandtabs;
input ID	Group $ Subgroup $;
cards;
101	A	AB
102	A	CD
103	A	EF
101	A	GH
104	A	IJ
102	A	KL
501	B	MN
521	B	OP
531	B	QR
501	B	ST
531	B	UV
540	B	WX
;
run;
data have;
 set have;
 by group;
 array x{999999} _temporary_;
 if first.group then do;n=0;call missing(of x{*});end;
 if id not in x then do;n+1;x{n}=id;end;
  else id=0;
run;

Re: How to create table on this data?

Ganeshk — Fri, 06 Jan 2017 08:05:54 GMT

Hi,

I got this output using simple technique:

Proc Sort data=have out=result nodupkey;
by Group ID;
Run;

Any how i tried these possibility also thanks.