topic Re: how to reverse a name with space and extract part of it? in SAS Programming

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

RavenWu — Sat, 09 Jul 2016 09:31:30 GMT

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:

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)

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

stat_sas — Sat, 09 Jul 2016 10:14:21 GMT

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

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

Tom — Sat, 09 Jul 2016 12:07:30 GMT

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=

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

Reeza — Sat, 09 Jul 2016 13:27:35 GMT

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.

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

RavenWu — Sat, 09 Jul 2016 20:10:54 GMT

Yes, it works. Thank you!!!