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
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
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
proc transpose data=test out=test2;
id col;
var text;
by n;
run;
Then edit the data as you like.
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
proc transpose data=test out=test2 name=column;
var 1-3;
run;
@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?
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: