topic Re: Grouping variables in SAS Programming

Grouping variables

Solvej — Thu, 22 Mar 2018 13:54:09 GMT

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

Re: Grouping variables

TomKari — Thu, 22 Mar 2018 14:07:29 GMT

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

Re: Grouping variables

Solvej — Thu, 22 Mar 2018 14:13:34 GMT

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

Re: Grouping variables

anandgupta22 — Thu, 22 Mar 2018 14:27:21 GMT

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

Then edit the data as you like.

Re: Grouping variables

Solvej — Thu, 22 Mar 2018 14:30:42 GMT

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

Re: Grouping variables

anandgupta22 — Thu, 22 Mar 2018 14:38:34 GMT

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

Re: Grouping variables

Reeza — Thu, 22 Mar 2018 17:24:14 GMT

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

Re: Grouping variables

Reeza — Thu, 22 Mar 2018 17:37:49 GMT

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;

Re: Grouping variables

novinosrin — Fri, 23 Mar 2018 01:17:21 GMT

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;

Re: Grouping variables

novinosrin — Fri, 23 Mar 2018 01:30:39 GMT

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;