Hi All,
I want to get rid of 'Psy' and 'His - 1' from my table, make all rows left align and get rid of spaces within the value (eg: Eng - 1 should be Eng1). Can someone help me please. Thanks
St_ID Course1 Course2 Course3
1234 Math - 1 Psy Eng - 1
5678 Eng - 2 Math - 2 Geo
3456 Psy Eng - 1 Phy
2345 Che - 1 His - 1 Math - 2
Output table should look like this:
St_ID Course1 Course2 Course3
1234 Math-1 Eng-1
5678 Eng-2 Math-2 Geo
3456 Eng-1 Phy
2345 Che-1 Math-2
data have;
input St_ID (Course1 Course2 Course3) ( & $20.);
cards;
1234 Math - 1 Psy Eng - 1
5678 Eng - 2 Math - 2 Geo
3456 Psy Eng - 1 Phy
2345 Che - 1 His - 1 Math - 2
;
run;
data want;
set have;
array c{3} $ 40;
array cour{*} $ Course1-Course3;
j=0;
do i=1 to dim(cour);
if cour{i} not in: ('His' 'Psy') and not missing(cour{i}) then do;
j+1;c{j}=compress(cour{i});
end;
end;
drop i j Course1-Course3;
run;
COMPRESS()
data have;
input St_ID (Course1 Course2 Course3) ( & $20.);
cards;
1234 Math - 1 Psy Eng - 1
5678 Eng - 2 Math - 2 Geo
3456 Psy Eng - 1 Phy
2345 Che - 1 His - 1 Math - 2
;
run;
data want;
set have;
array c{3} $ 40;
array cour{*} $ Course1-Course3;
j=0;
do i=1 to dim(cour);
if cour{i} not in: ('His' 'Psy') and not missing(cour{i}) then do;
j+1;c{j}=compress(cour{i});
end;
end;
drop i j Course1-Course3;
run;
Or use transpose, twice
data have;
input St_ID (Course1 Course2 Course3) ( & $20.);
cards;
1234 Math - 1 Psy Eng - 1
5678 Eng - 2 Math - 2 Geo
3456 Psy Eng - 1 Phy
2345 Che - 1 His - 1 Math - 2
;
proc transpose data=have out=list(drop=_name_);
var course:;
by st_id notsorted;
run;
data newList;
set list;
where col1 not in ("Psy","His - 1");
col1 = compress(col1," -");
run;
proc transpose data=newList out=want(drop=_name_) prefix=Course;
by st_id notsorted;
var col1;
run;
Yes, as @PGStats pointed out transposing your data twice achieves the desired result. However its worth pointing out that your code is more complicated due to the fact that your working with a transposed data set, i.e. the variables are going across rather than down. Whilst you are processing the data, i.e. not for an output report, it is generally a good idea to keep your data normalised as this allows for simpler code, easier grouping, and by group processing, and you can see that this problem, if your data is in a good format, is a very simple datastep.
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.