BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buddha_d
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

12 REPLIES 12
buddha_d
Pyrite | Level 9

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
Quartz | Level 8

Hi,

 

Could you please provide the proc import code here?

Did you try infile in datastep apart from proc import?

 

buddha_d
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Opal | Level 21

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
Pyrite | Level 9

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

buddha_d
Pyrite | Level 9
yes it does, Thanks KurtBremser
Tom
Super User Tom
Super User

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.

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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