topic use of tranwrd or translate function in my case in SAS Programming

use of tranwrd or translate function in my case

buddha_d — Fri, 14 Jul 2017 03:11:44 GMT

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

input text qualifiers ????

buddha_d — Fri, 14 Jul 2017 01:46:22 GMT

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.

Re: use of tranwrd or translate function in my case

art297 — Fri, 14 Jul 2017 03:36:07 GMT

Here is one way:

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

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

Art, CEO, AnalystFinder.com

Re: use of tranwrd or translate function in my case

buddha_d — Fri, 14 Jul 2017 03:41:19 GMT

Thanks Art, It works. yeh thanks again

Re: use of tranwrd or translate function in my case

Tom — Fri, 14 Jul 2017 04:18:48 GMT

Neither. Use HTMLDECODE() function.

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

store=macy&apos;s store2=macy's
store=Barnes &amp; Nobles store2=Barnes & Nobles
store=chevy&apos;s store2=chevy's
store=dave &amp; 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 '&AMP;' and other combinations of case.

Re: input text qualifiers ????

chithra — Fri, 14 Jul 2017 04:49:28 GMT

Hi,

Could you please provide the proc import code here?

Did you try infile in datastep apart from proc import?

Re: input text qualifiers ????

Kurt_Bremser — Fri, 14 Jul 2017 07:10:42 GMT

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.

Re: use of tranwrd or translate function in my case

Kurt_Bremser — Fri, 14 Jul 2017 07:16:28 GMT

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

Re: use of tranwrd or translate function in my case

Kurt_Bremser — Fri, 14 Jul 2017 07:18:38 GMT

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

Re: use of tranwrd or translate function in my case

buddha_d — Fri, 14 Jul 2017 11:33:46 GMT

yes it does, Thanks KurtBremser

Re: input text qualifiers ????

buddha_d — Fri, 14 Jul 2017 12:06:03 GMT

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;

Re: input text qualifiers ????

buddha_d — Fri, 14 Jul 2017 12:07:48 GMT

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.

Re: use of tranwrd or translate function in my case

buddha_d — Fri, 14 Jul 2017 17:18:06 GMT

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