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
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
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.
Hi,
Could you please provide the proc import code here?
Did you try infile in datastep apart from proc import?
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;
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.
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.
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
Thanks Art, It works. yeh thanks again
HTMLDECODE covers all HTML special character encodings. With tranwrd, you would have to supply all encodings yourself.
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.
I merged the two questions dealing with the same basic problem.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.