BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RavenWu
Calcite | Level 5

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)

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
stat_sas
Ammonite | Level 13

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 
Tom
Super User Tom
Super User

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=

 

Reeza
Super User

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. 

RavenWu
Calcite | Level 5
Yes, it works. Thank you!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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