DATA Step, Macro, Functions and more

use of tranwrd or translate function in my case

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 100
Accepted Solution

use of tranwrd or translate function in my case

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


Accepted Solutions
Solution
‎07-13-2017 11:41 PM
PROC Star
Posts: 7,474

Re: use of tranwrd or translate function in my case

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

 

View solution in original post


All Replies
Frequent Contributor
Posts: 100

input text qualifiers ????

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. 

 

 

Occasional Contributor
Posts: 8

Re: input text qualifiers ????

Hi,

 

Could you please provide the proc import code here?

Did you try infile in datastep apart from proc import?

 

Frequent Contributor
Posts: 100

Re: input text qualifiers ????

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;

Super User
Posts: 7,782

Re: input text qualifiers ????

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 100

Re: input text qualifiers ????

Posted in reply to KurtBremser

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. 

Solution
‎07-13-2017 11:41 PM
PROC Star
Posts: 7,474

Re: use of tranwrd or translate function in my case

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

 

Frequent Contributor
Posts: 100

Re: use of tranwrd or translate function in my case

Thanks Art, It works. yeh thanks again Smiley Very Happy

Super User
Posts: 7,782

Re: use of tranwrd or translate function in my case

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 100

Re: use of tranwrd or translate function in my case

Posted in reply to KurtBremser
yes it does, Thanks KurtBremser
Super User
Super User
Posts: 7,050

Re: use of tranwrd or translate function in my case

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.

Super User
Posts: 7,782

Re: use of tranwrd or translate function in my case

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 100

Re: use of tranwrd or translate function in my case

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

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

Discussion stats
  • 12 replies
  • 406 views
  • 5 likes
  • 5 in conversation