DATA Step, Macro, Functions and more

how to reverse a name with space and extract part of it?

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

how to reverse a name with space and extract part of it?

[ Edited ]

I want to extract the first name from a name with a space and also reverse it, and here is my codes:

 

 PROC IMPORT DATAFILE='D:\Wow.xlsx' OUT= Wow  DBMS= XLSX  REPLACE;
 RUN;

 DATA Names;
 SET  Wow;
 First_Name = SCAN(REVERSE(Reversed_names),1);
 RUN;

The result is:

my result.png

 

The content is empty, which is not what I want. Could anyone tell me what is the problem with my codes? (see attachment with the dataset)

 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎07-09-2016 04:10 PM
Super User
Posts: 17,819

Re: how to reverse a name with space and extract part of it?

I think @stat_sas is close. 

 

 Check your variable properties. Since you're using EG, it looks like it's allowing spaces in the variable name. 

So you need to refer to your variable as 

 

'Reversed Names'n

 

or set the following option so it converts spaces to underscores when you import your data. 

 

Option validvarname = v7;

Otherwise your code is correct. 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: how to reverse a name with space and extract part of it?

Hi,

 

This works fine for me. 

 

data Wow;
input Name $20.;
datalines;
lazraM sérdnA
ladiV euqirnE
;

 

DATA Names;
SET Wow;
First_Name = SCAN(REVERSE(Name),1);
RUN;

 

Problem seems to be in variable reference

 

SCAN(REVERSE(Reversed_names),1);


 

Variable Reversed_names is not contained in your data set wow 
Super User
Super User
Posts: 6,499

Re: how to reverse a name with space and extract part of it?

[ Edited ]

Your code is fine. It is proabably your data that is messed up.  Most likely you have some non-printing charater at the end of the line that is being interpretted by SCAN() as the first word.

 

Your XLSX file is fine, but perhaps you actually read the data from a text file and did not properly tell SAS what characters to use for end of line and ended up with a Carriage Return character at the end of each value?

 

libname x xlsx "&path/Wow.xlsx";
data want;
 set x.sheet1 ;
 first_name= scan(reverse(reversed_names),1);
 put (_all_) (=);
 first_name= scan(reverse(reversed_names||'0D'x),1);
 put (_all_) (=);
run;

Results

Reversed_Names=lazraM sérdnA first_name=Andrés
Reversed_Names=lazraM sérdnA first_name=

Reversed_Names=ladiV euqirnE first_name=Enrique
Reversed_Names=ladiV euqirnE first_name=

 

Solution
‎07-09-2016 04:10 PM
Super User
Posts: 17,819

Re: how to reverse a name with space and extract part of it?

I think @stat_sas is close. 

 

 Check your variable properties. Since you're using EG, it looks like it's allowing spaces in the variable name. 

So you need to refer to your variable as 

 

'Reversed Names'n

 

or set the following option so it converts spaces to underscores when you import your data. 

 

Option validvarname = v7;

Otherwise your code is correct. 

Contributor
Posts: 20

Re: how to reverse a name with space and extract part of it?

Yes, it works. Thank you!!!
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 333 views
  • 0 likes
  • 4 in conversation