Remove carriage return and line feed characters within quoted strings

/* libname source “/home/…"; */

/* input correct capital letters to path and file name start position */

%let source_path=/home/…;

%let filename_start_position=20;

/* option */

options mprint symbolgen;

%marco clean_data(fname);

%let dsname="&source_path./&fname.";

/**Sample 1647: Remove carriage return and line feed characters within quoted strings */

/* ‘0D’x is the hexadecimal representation of CR and  */

/* ‘0A’x is the hexadecimal representation of LF.  */

/* ‘2C’x is the hexadecimal representation of Comma  */

/* ************ CAUTION ************************/

/* */

/* This program UPDATES IN PLACE, keep a bakcup copy before running. */

/* */

/* ************ CAUTION ************************/

/* Replace carriage return and linefeed characters inside */

/* double quotes with a specified character. This sample */

/* uses ‘@’ and ‘$’, but any character can be used, including */

/* spaces. CR/LFs not in double quotes will not be replaced. */

%let repA=’@'; /* replacement character LF */

%let repD=’$'; /* replacement character CR */

%let repC=':'; /* replacement character Comma */

data _null_;

/* RECFM=N reads the file in binary format. The file consists */

/* of a stream of bytes with no record boundaries */

infile &dsname recfm=n;

file &dsname recfm=n;

/* OPEN is a flag variable used to determine if the CR/LF is within */

/* double quotes or not. Retain this value. */

retain open 0;

input a $char1.;

/* If the character is a double quote, set OPEN to its opposite value. */

if a = ‘"‘ then open = ^(open);

/* If the CR or LF is after an open double quote, replace the byte with */

/* the propriate value. */

if open then do;

if a = ‘0D’x then do;

put &repD;

end;

else if a = ‘0A’x then do;

put &repA;

end;

else if a = ‘2C’x then do;

put &repC;

end;

end;

run;

/*** read into sas file ***/

/*

data &fname._new;

infile &dsname dlm=’,’ dsd missover firstobs=15 obs=25;

input line: $144. ;

run;

*/

%put &dsname;

%mend clean_data;

/*** for single text file ***/

/* %clean_data(123.txt); */

/* remove macro statement file */

filename remove pipe “rm &source_path/cleaninglist.sas";

data _null_;

file remove;

stop;

run;

/* create macro statement file */

filename cmd PIPE “ls &source_path/*.txt";

data files;

infile cmd trunover;

input @&filename_start_position name $50.;

run;

/*** print put data ***/

/* proc print data = files;

run; */

/* write repeat call statements into sas program file */

options ls=200;

data makecode; set files;

lines = ‘%clean_data(‘||trim(name)||’);';

file “&source_path/cleaninglist.sas";

put lines;

run;

/* embed repeat call statements */

%include “&source_path/cleaninglist.sas";

run; quit;

三遊大板

Day 1 – 關西機場落機。小雪。 乘巴士往 RINKU PREMIUM OUTLETS。 買了兒童衣服的福袋. 吃過了日式飯及餃子之後。就乘JR轉了好幾個地鐵站到酒店。其實在心齋橋行一個站到酒店會比較快。20150101_18121020150101_18294020150101_19311720150101_193742

Day 2 – 早餐 Mos Burger。去Orange Street 太多人排 Ape 店。走過去 黑門魚市場。 因假期大多數都沒開。最後還是在心齋橋百貨公司走呀走。

20150102_12321420150102_13250420150102_13312020150102_13470220150102_09042920150102_09034420150102_090332

Day 3 – 去神戶麵包超人館。吃過午飯。再到垂水OUTLET。晚餐在道頓堀吃壽司。

20150103_09182620150103_09245620150103_09283920150103_09413720150103_10362520150103_10332720150103_10332120150103_10021420150103_10022720150103_10022120150103_10043720150103_10393520150103_10384020150103_10381820150103_11302320150103_11395920150103_12223520150103_12224920150103_13052120150103_17024720150103_19003320150103_20245820150103_21072320150103_083227

Day4 – 環球影城。再回來心齋橋吃和牛。

20150104_09012620150104_09174020150104_09250820150104_09265220150104_09272820150104_09285320150104_09285820150104_09293020150104_10292320150104_10312920150104_10320220150104_10370220150104_10380620150104_11392320150104_17013220150104_12510020150104_12513120150104_142734FB_IMG_1420388512884FB_IMG_1420388503217

Day 5 – 去黑門魚市場。 因店子細小。Baby 車和旅行箱也放不下。又回去道頓堀吃壽司。之後就到難波站到關西機場。

20150105_09590220150105_09591220150105_140453

吳若希 – 越難越愛 (TVB劇集"使徒行者"片尾曲)

作詞:張美賢 作曲:徐洛鏘 編曲:Johnny Yim 監製:Johnny Yim/韋景雲

世界上哪裡去找熱望熱似火 不褪落 我對你心跳的感覺 高低跌盪
最困惑那半秒鐘念在有你的 寬廣的肩膊 有勇氣踏前覓幸福 從無回望
再計算也沒一種方法 定論什麼 講對或錯 只需知道 別連累你對我失望

無懼世事變改 還是越難越愛 為你所以在期待 花開花落至少這一季 被甜蜜蓋過了傷害
無負跳入愛海 沉重越來越愛 就算一切沒記載 軟弱會再有氣慨 跟處境比賽 在途上就算有些感慨
讓我 學會抱緊撐到未來 別讓手放開

最困惑那半秒鐘念在有你的 寬廣的肩膊 有勇氣踏前覓幸福 我要與你去看極地曙光
數百個也沒身份資格 定論什麼 阻我理想 只需知道 別要讓你對我失望

無懼世事變改 還是越難越愛 為你所以在期待 花開花落至少這一季 被甜蜜蓋過了傷害
無負跳入愛海 沉重越來越愛 就算一切沒記載 軟弱會再有氣慨 跟處境比賽
命途沒法割開 和你綑綁 在面對多少意外

世事變改 還是越難越愛 願挺身去保衛愛 花開花落至少這一季 被甜蜜蓋過了傷害
無負跳入愛海 沉重越來越愛 浪已將眼淚覆蓋 軟弱會再有氣慨 跟處境比賽 命途沒法割開
懷抱的手 我不想再放開

林奕匡 (Phil Lam) – 高山低谷

作曲:林奕匡 作詞:陳詠謙  編曲:Edward Chan / 黃兆銘 監製:Edward Chan

站在樹林內 就如沒氧氣 在夕陽下 寂寥吧 沒權利見你
早知高的山低的谷將你我分隔兩地 失去人情味
你那貴族遊戲 我的街角遊記 天真到信真心 太兒戲

**你快樂過生活 我拼命去生存 幾多人位於山之巔俯瞰我的疲倦
渴望被成全 努力做人誰怕氣喘 但那終點 掛在那天邊
你界定了生活 我侮辱了生存 只適宜滯於山之谷整理我的凌亂
渴望大團圓 腳下路程難以削短 未見終點 也未見恩典 我與你極遠

愈望愈無望 未來沒有我 在斷崖下 盡頭吧 樂園未有過
彷彿天一黑天一光揮發了一句再會 只見人下墮
快慰繼續傳播 你都不慰問我 區分到太清楚 太嚴苛

Repeat**

我卻尚要生存 偷偷存活於山之谷等到某天魂斷
你繼續盤旋 我繼續埋藏我愛戀
沒有終點 永沒有終點 那永遠極遠

Excel isolate the last word in a string

 

Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!

 

Trim B2 first

=IF(ISERROR(FIND(" “,B2)),B2, RIGHT(B2,LEN(B2) – FIND(“|",
SUBSTITUTE(B2," “,"|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," “,""))))))

 

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) – Count of spaces in the original string

SUBSTITUTE(A1," ","|", ... ) – Replaces just the final space with a |
FIND("|", ... ) – Finds the absolute position of that replaced | (that was the final space)
Right(A1,LEN(A1) - ... )) – Returns all characters after that |

 

source from