DATA Step, Macro, Functions and more

Grouping variables

Reply
Contributor
Posts: 29

Grouping variables

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.

 

ntextColumn i would like
1ab1
2abc1
3ad2
4abc1
5hi3

 

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

123
abadhi
abc  

 

I hope you can help.

 

Solvej

PROC Star
Posts: 1,333

Re: Grouping variables

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

Contributor
Posts: 29

Re: Grouping variables

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

New Contributor
Posts: 2

Re: Grouping variables


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

 

Then edit the data as you like.

Contributor
Posts: 29

Re: Grouping variables

Posted in reply to anandgupta22

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

New Contributor
Posts: 2

Re: Grouping variables

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

Super User
Posts: 23,958

Re: Grouping variables


@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?

Super User
Posts: 23,958

Re: Grouping variables

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;
Super User
Posts: 2,046

Re: Grouping variables

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;
Super User
Posts: 2,046

Re: Grouping variables

Posted in reply to 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;
Ask a Question
Discussion stats
  • 9 replies
  • 169 views
  • 2 likes
  • 5 in conversation