Grouping variables

[Solvej]

Hi SAS Experts

I would like to group a variable that contains a wide range of "text". I have the grouping of the variables in another dataset where the different versions of the text that needs to be grouped together is listed in different variables. How can I do this?

 I'm using Base SAS 9.4

This is an example of the data I have and that I would like to recode. I the real world there are 1000s of lines.

n	text	Column i would like
1	ab	1
2	abc	1
3	ad	2
4	abc	1
5	hi	3

This is an example of the other dataset that I have which should define the recoding.

1	2	3
ab	ad	hi
abc

I hope you can help.

Solvej

[TomKari]

So does the next row with "Column I would like" equal to 2 go beside "abc" in your result?

This seems to me like an odd algorithm...I think something may be missing. How do your "column 2 and 3" values match up to the correct "column 1" values?

Tom

[Solvej]

Hi Tom

There is no relationship between the different columns in the second dataset. Basically it is just a three different lists of text bits where the header is the number I would like to implement in data set 1.

I hope this makes sense,

Solvej

[anandgupta22]

proc transpose data=test out=test2;
id col;
var text;
by n;
run;

Then edit the data as you like.

[Solvej]

Dear anandgupta22

Thank you for your answer but I dont think that is sufficient. I want to use the information from dataset 2 to recode in data 1. I the real World both dataset 1 and 2 are very complicated.

Solvej

[anandgupta22]

proc transpose data=test out=test2 name=column;
var 1-3;
run;

[Reeza]

---

@Solvej wrote:

Dear anandgupta22

Thank you for your answer but I dont think that is sufficient. I want to use the information from dataset 2 to recode in data 1. I the real World both dataset 1 and 2 are very complicated.

Solvej

---

Is your example representative of your problem.

I think the answer from @anandgupta22 is correct though. Transpose Table 2 and then a left join/hash/format should be all you need. Is there some reason you think it's more complex than that?

[Reeza]

This works exactly as you have described.

If it doesn't work on your data, please explain in detail how it does not work and include sample records that illustrate how.

data table1;
input n text $;
cards;
1	ab	
2	abc	
3	ad	
4	abc	
5	hi	
;
run;

data table2;
infile cards truncover;
input group1 $ group2 $ group3 $ ;
cards;
ab ad hi
abc
;
run;

data group_fmt_data;
set table2;
array g(3) $ group1-group3;
type='C';
fmtname='group_fmt';
do i=1 to dim(g);
Label = i;
Start = g(i);
if not missing(Start) then output;
end;

keep type fmtname label start;
run;

proc format cntlin=group_fmt_data;
run;
data want;
set table1;
group = put(text, group_fmt.);
run;

[novinosrin]

data table1;
input n text $;
cards;
1	ab	
2	abc	
3	ad	
4	abc	
5	hi	
;
run;

data table2;
infile cards truncover;
input group1 $ group2 $ group3 $ ;
cards;
ab ad hi
abc
;
run;

data want;
if _n_=1 then do;
if 0 then do;set table1;set table2;end;
dcl hash H (dataset:'table1',multidata:'y') ;
   h.definekey  ('text') ;
   h.definedata ('text','n') ;
   h.definedone () ;
dcl hash H1 (dataset:'table2') ;
   h1.definekey  ('group1', 'group2','group3') ;
   h1.definedone () ;
declare hiter iter('h1');
   end;
array grp(*) group:;
rc = iter.first();
do while (rc = 0);
	do _n_=1 to dim(grp);
 		do while(h.do_over(key:grp(_n_)) eq 0);
  			Column_you_would_like=vname(grp(_n_));
  			output;
		end;
	
	end;
rc = iter.next();
end;
stop;
drop rc group:;
run;

[novinosrin]

Further simplified and easy:

data table1;
input n text $;
cards;
1	ab	
2	abc	
3	ad	
4	abc	
5	hi	
;
run;

data table2;
infile cards truncover;
input group1 $ group2 $ group3 $ ;
cards;
ab ad hi
abc
;
run;

data want;
if _n_=1 then do;
if 0 then set table2;
dcl hash H1 (dataset:'table2') ;
   h1.definekey  ('group1', 'group2','group3') ;
   h1.definedone () ;
declare hiter iter('h1');
   end;
set table1;
array grp(*) group:;
rc = iter.first();
do while (rc = 0);
	do _n_=1 to dim(grp);
 	if grp(_n_)=text then do; Column_you_would_like=vname(grp(_n_));output;return;end;
  	end;
rc = iter.next();
end;
stop;
drop rc group:;
run;