DATA Step, Macro, Functions and more

Removing some variables, left align and space removal

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

Removing some variables, left align and space removal

[ Edited ]

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


Accepted Solutions
Solution
‎03-16-2016 01:52 PM
Super User
Posts: 9,681

Re: Removing some variables, left align and space removal

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;

View solution in original post


All Replies
Super User
Posts: 17,836

Re: Removing some variables, left align and space removal

COMPRESS()

Solution
‎03-16-2016 01:52 PM
Super User
Posts: 9,681

Re: Removing some variables, left align and space removal

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;
Regular Contributor
Posts: 215

Re: Removing some variables, left align and space removal

Thanks Xia Keshan. Your code is really helpful.
Respected Advisor
Posts: 4,649

Re: Removing some variables, left align and space removal

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;
PG
Super User
Super User
Posts: 7,401

Re: Removing some variables, left align and space removal

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 318 views
  • 6 likes
  • 5 in conversation