BookmarkSubscribeRSS Feed
Solvej
Obsidian | Level 7

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

9 REPLIES 9
TomKari
Onyx | Level 15

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
Obsidian | Level 7

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
Calcite | Level 5


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

 

Then edit the data as you like.

Solvej
Obsidian | Level 7

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
Calcite | Level 5

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

Reeza
Super User

@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
Super User

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
Tourmaline | Level 20
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
Tourmaline | Level 20

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1191 views
  • 2 likes
  • 5 in conversation