Here is : Just change var group-- into var group: ; for referring to variables. data have; infile cards truncover; input ID (Name Group1 Group1_ID1 Group1_D2 Group2 Group2_ID1 Group2_D2 Group3 Group3_ID1 Group3_D2) (: $20.); cards; 101 Mike English Eng2 Eng1 Biology Bio1 102 Julia English Eng1 Eng2 Biology Bio1 Bio2 Chemistry Che1 Che2 103 Luc English Eng3 Eng1 Biology Bio1 Bio2 ; run; proc transpose data=have out=temp; by id; var group: ; run; data data(index=(x=(id g2)) drop=_name_) group_name(keep=_name_ col1); set temp; length g1 g2 $ 40; g1=upcase(scan(_name_,1,'_')); g2=upcase(scan(_name_,2,'_')); if not missing(g2) then output data; if missing(g2) and not missing(col1) then output group_name; run; proc sort data=group_name nodupkey;by _name_ col1;run; proc sql noprint; select cats(_name_,'=',col1) into : rename separated by ' ' from group_name; quit; %put &rename; proc sql noprint; select distinct catt('data(where=(g1="',g1,'") rename=(col1=',g1,'))') into : merge separated by ' ' from data; quit; %put &merge; data want(rename=(&rename)); merge &merge; by id g2; drop g1 g2; run;
Please try the transpose approach
data have;
infile cards missover;
input ID Name$ Group1$ Group1_ID1$ Group1_D2$ Group2$ Group2_ID1$ Group2_D2$ Group3$ Group3_ID1$ Group3_D2$;
cards;
101 Mike English Eng2 Eng1 Biology Bio1
102 Julia English Eng1 Eng2 Biology Bio1 Bio2 Chemistry Che1 Che2
103 Luc English Eng3 Eng1 Biology Bio1 Bio2
;
proc transpose data=have out=trans1;
by id;
id group1 ;
var Group1_ID1 Group1_D2 ;
run;
proc transpose data=have out=trans2;
by id;
id group2 ;
var Group2_ID1 Group2_D2 ;
run;
proc transpose data=have out=trans3;
by id;
id group3 ;
var Group3_ID1 Group3_D2 ;
run;
data all(drop=_name_);
merge trans1 trans2 trans3;
run;
we could automate with do loop and call execute as below
option mprint;
%macro test(i);
proc transpose data=have out=trans&i;
where group&i ne '';
by id;
id group&i ;
var Group&i._ID1 Group&i._D2 ;
run;
%mend;
Data _null_;
do i = 1 to 35;
call execute('%test('||i||')');
end;
run;
data all(drop=_name_);
merge trans: ;
run;
I am not sure if trans: (colon) will work. could you please test it.
I love this question. Why the output couldn't be : ID English Biology Chemistry 101 Eng1 101 Eng2 Bio1 102 Eng2 Bio2 Che2 102 Eng1 Bio1 Che1 103 Eng1 Bio2 103 Eng3 Bio1 data have; infile cards truncover; input ID (Name Group1 Group1_ID1 Group1_D2 Group2 Group2_ID1 Group2_D2 Group3 Group3_ID1 Group3_D2) (: $20.); cards; 101 Mike English Eng2 Eng1 Biology Bio1 102 Julia English Eng1 Eng2 Biology Bio1 Bio2 Chemistry Che1 Che2 103 Luc English Eng3 Eng1 Biology Bio1 Bio2 ; run; proc transpose data=have out=temp; by id; var group1--Group3_D2; run; data data(index=(x=(id g2)) drop=_name_) group_name(keep=_name_ col1); set temp; length g1 g2 $ 40; g1=upcase(scan(_name_,1,'_')); g2=upcase(scan(_name_,2,'_')); if not missing(g2) then output data; if missing(g2) and not missing(col1) then output group_name; run; proc sort data=group_name nodupkey;by _name_ col1;run; proc sql noprint; select cats(_name_,'=',col1) into : rename separated by ' ' from group_name; quit; %put &rename; proc sql noprint; select distinct catt('data(where=(g1="',g1,'") rename=(col1=',g1,'))') into : merge separated by ' ' from data; quit; %put &merge; data want(rename=(&rename)); merge &merge; by id g2; drop g1 g2; run;
Here is : Just change var group-- into var group: ; for referring to variables. data have; infile cards truncover; input ID (Name Group1 Group1_ID1 Group1_D2 Group2 Group2_ID1 Group2_D2 Group3 Group3_ID1 Group3_D2) (: $20.); cards; 101 Mike English Eng2 Eng1 Biology Bio1 102 Julia English Eng1 Eng2 Biology Bio1 Bio2 Chemistry Che1 Che2 103 Luc English Eng3 Eng1 Biology Bio1 Bio2 ; run; proc transpose data=have out=temp; by id; var group: ; run; data data(index=(x=(id g2)) drop=_name_) group_name(keep=_name_ col1); set temp; length g1 g2 $ 40; g1=upcase(scan(_name_,1,'_')); g2=upcase(scan(_name_,2,'_')); if not missing(g2) then output data; if missing(g2) and not missing(col1) then output group_name; run; proc sort data=group_name nodupkey;by _name_ col1;run; proc sql noprint; select cats(_name_,'=',col1) into : rename separated by ' ' from group_name; quit; %put &rename; proc sql noprint; select distinct catt('data(where=(g1="',g1,'") rename=(col1=',g1,'))') into : merge separated by ' ' from data; quit; %put &merge; data want(rename=(&rename)); merge &merge; by id g2; drop g1 g2; run;
It is a good idea indeed to simplify your data structure. Another transposition approach:
data have;
length Group1 Group1_ID1 Group1_D2 Group2 Group2_ID1 Group2_D2 Group3 Group3_ID1 Group3_D2 $20;
infile cards missover;
input ID Name$ Group1 Group1_ID1 Group1_D2 Group2 Group2_ID1 Group2_D2 Group3 Group3_ID1 Group3_D2;
cards;
101 Mike English Eng2 Eng1 Biology Bio1
102 Julia English Eng1 Eng2 Biology Bio1 Bio2 Chemistry Che1 Che2
103 Luc English Eng3 Eng1 Biology Bio1 Bio2
;
data list0;
length gName gExt gValue $20;
set have;
array g group:;
do i = 1 to dim(g);
if not missing(g{i}) then do;
gName = scan(vname(g{i}), 1, "_");
gExt = scan(vname(g{i}), 2, "_");
gValue = g{i};
output;
end;
end;
keep id gName gExt gValue;
run;
proc sort data=list0; by id gName gExt; run;
data list1;
do until(last.gName);
set list0; by id gName;
if first.gName then var = gValue;
else output;
end;
run;
proc sort data=list1; by id gExt;
proc transpose data=list1 out=want(drop=_name_);
by id gExt;
var gValue;
id var;
run;
proc print data=want noobs; run;
I'd actually suggest you go one step further and include the subject as a variable.
ID Subject Level
1 English Eng1
1 English Eng2
1 Chemistry Chem2
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.