use of tranwrd or translate function in my case

[buddha_d]

I have the following input_names data in a single column

macy's

Barnes & Nobles

chevy's

dave & buster

I want to see the output_names in SAS as this

macy's

Barnes & Nobles

chevy's

dave & buster

what function do I need to use to get this output. 

Thanks

[buddha_d]

Dear SAS community,

                    I have problem importing data from excel (used Proc Import) and the text string in excel (eg: wendy's) is showing in sas data as wendy ' s (another example is &: if the name is Barnes & Nobles in excel , SASEG6.4 is reading as Barnes& Nobles ).

                  I heard from someone that we can set input qualifiers in SAS  and tell it how to read? Does anyone have any idea. 

Your suggestions are much appreciated. 

[chithra]

Hi,

Could you please provide the proc import code here?

Did you try infile in datastep apart from proc import?

[buddha_d]

Hi chitra, I am sending proc import code, I tried datastep with infile and I failed badly. I am adding excel file, Thanks, 

DM'LOG;CLEAR;OUT;CLEAR';
PROC IMPORT OUT= WORK.work
DATAFILE= "C:\Users\SAS\Desktop\DUMMY DATA.xlsx"
DBMS=excel replace;
/* datarow=20;*/
/* DATAROW=19;*/
RANGE="Accounting$";
GETnaMES=yes;
MIXED=yes;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
data work.daya;
retain start_date;
length START_DATE 8 TNUMBER $64. DATE_TIME $22. DUES $35. TIN_NUMBER $15. TELLER $6. SELLOR $30. BALANCE $15. TRDATE $10.
SHOP $75. SNUMBER $25. ADDRESS $75. TOWN $50. STATE $10. ZIPCODE $10. DBALANCE 8;

set work (rename=(F1=TNUMBER F2=DATE_TIME F3=DUES F4=TIN_NUMBER F5=TELLER ACCOUNTING_FOR_DATABASE=SELLOR F7=BALANCE F8=TRDATE
datetime__07_11_2016_09_30_55=SHOP F10=SNUMBER F11=ADDRESS F12=TOWN F13=STATE F14=ZIPCODE));

if _n_=6 then start_date=input(compress(scan(TNUMBER,2,':')),mmddyy10.);

if _n_>=20;
DBALANCE=input(COMPRESS(BALANCE,'$,'),best.);
dtrdate=input(trdate,mmddyy10.);
ddate_time=input(compress(scan(date_time,1," ")), mmddyy10.);
format start_date dtrdate ddate_time mmddyy10.;
attrib _all_ label=" ";
run;

[Kurt_Bremser]

Use the htmldecode() function in a data step after the import; if you use a proper data transfer format, you can incorporate it into the data step that reads the text file.

[buddha_d]

Thank you Kurt. I will try that option and let you know if it works or not. 

Your suggestions always worked for me. So I am hoping this works as well. Thanks and great to have you in the SAS community. 

[art297 · Accepted solution]

Here is one way:

data have;
  informat store $50.;
  input store &;
  cards4;
macy's
Barnes & Nobles
chevy's
dave & buster
;;;;

data want;
  set have;
  store=tranwrd(tranwrd(store,"%nrstr(&)","&"),"%nrstr(')","'");
run;

Art, CEO, AnalystFinder.com

[buddha_d]

Thanks Art, It works. yeh thanks again 

[Kurt_Bremser]

HTMLDECODE covers all HTML special character encodings. With tranwrd, you would have to supply all encodings yourself.

[buddha_d]

yes it does, Thanks KurtBremser

[Tom]

Neither. Use HTMLDECODE() function.

11   data have;
12     input store $50.;
13     store2 = htmldecode(store);
14     put (_all_) (=);
15   cards4;

store=macy's store2=macy's
store=Barnes & Nobles store2=Barnes & Nobles
store=chevy's store2=chevy's
store=dave & buster store2=dave & buster
NOTE: The data set WORK.HAVE has 4 observations and 2 variables.

TRANSLATE() is for translating individual charactrers, so it would not work.

TRANWRD() could work, but you would need to test not just for '&', but also '&' and other combinations of case.

[Kurt_Bremser]

I merged the two questions dealing with the same basic problem.

[buddha_d]

KurtBremser, htmldecode() worked like wonders, all the crap that I was seeing was gone from the data. Thanks